How is sql string generated in LightSpeed? table name comes from ?

Hello All,

In the project I am working on, there a base entity class(says CommonBase) which is an abstract class implements an interface ICommon,but Database does not have a table called CommonBase, and there is another entity class(says Object) which implements Interface ICommon as well,and table Object exists in Database,I created a method Delete in ObjectRepository ,code like following,

Object object = GetObjectById(id);

if(object!=null) {

UnitOfWork.Remove(object);---------- Throw sql exception "Invalid object name CommonBase"

UnitOfWork.SaveChanges(); } As LogSql method ,I catched the sql string looks like

SELECT [CommonBase].[Id] AS [CommonBase.Id] FROM [CommonBase] WHERE [CommonBase].[ObjectReferenceId] = 'xxx-xxxx-xxxx-xxxx',

Does anyone have idea why was the SQL string generated base on name "CommonBase" instead of "Object"?

Thank you,

Charles

We don't really have enough information to answer this question -- we don't know how Object relates to CommonBase or what ObjectReferenceId is.

However, guessing from the generated SQL, it looks like you might have an association to a concrete table inheritance base class. This is not allowed and I'm surprised you're not getting errors earlier. Concrete table inheritance base classes may contain only scalar fields. If this is the case, move the association to the leaf classes.

Otherwise, please provide a minimal repro (a console application or NUnit test containing only the code and minimum set of entities required to reproduce the error) and we should be able to help you out!

Hi Ivan,

I sent the reproduce code to your support email address.

Thank you,

Charles

Hello Charles,

Your model contains 58 entity classes, which makes it very difficult for us to track down the issue. If you can prune this back to a minimal repro, containing only the classes needed to reproduce the error, it will enable us to address your issue much more promptly. Thanks!

Hello Ivan,

I minimized the repro testing project and sent to your support email address,I am not sure if you need database ,because that sql exception throwed because there was not specified table 'ThingBase' in database.I think in your side ,you might be able to track the code of sql string generating to find out the sql string created base on 'ThingBase' instead of 'Thing'.

Thank you so much for your work,

Charles

Hello Charles,

Thanks for the repro. The problem is that you are trying to use an association to a concrete table inheritance base class in a polymorphic way. In order to delete a Thing, we need to determine what other entities are dependent on that Thing. That means finding out which other entities have a foreign key to that Thing. We do this by looking at the to-many associations (EntityCollections) of the Thing, on the theory that any foreign key will be found in the table on the other end of the to-many association.

This is where the ThingBase query is coming from: we see the Things association to ThingBase, so we look at the 'table' on the far end -- the ThingBase 'table' -- to find out if there are any entities there that need cascade deleting. Of course, this results in an error because there is no ThingBase table.

Unfortunately, there is no good resolution to this. We could attempt to query all the tables derived from ThingBase, but I'm not sure we have this information, and in your case that could be a large number of tables. Or we could say that in this situation it's up to the application (or database) to do any required cascade deletes. Neither seems really ideal; we're open to feedback.

The issue is that LightSpeed is automatically looking back along the one way association and is generating SQL to try and delete the base table which will never work.

The deleting entity has a type on it which can be used to delete the associated entity manually (there is no foreign key so delete order doesn't matter).

How do I stop LS trying to automatically delete back along the one way association?

So you are happy with option 2, that LightSpeed should not attempt to delete dependent entities in this case, and it will be up to the application to delete them manually? If so, let me know and I should be able to get that added for the next nightly.

Yes but the change should be to only not delete the concrete base class dependent entities. There are other entities with a relationship that don't use inheritance that are successfully being deleted.

Yep, understood.

What you need to do is put [RemovalBehavior(Action = RemovalBehaviorAction.NoActionNoCheck)] on the other end of the association (in your case. the ThingBase._thingReference field). This excludes just that association from cascade deletes. Note you must apply this to the EntityHolder field at the FK end, not the EntityCollection field at the parent end.

However, if you do this now, you'll get a validation error saying that NoActionNoCheck can be applied only to recursive associations. The next nightly will remove this limitation and allow it also to be used on one-way associations, which should see you right.

Also, from my testing, when deleting a Thing and all its children, you may need to save the child deletes before saving the parent delete, rather than doing them in a single SaveChanges. This is due to a limitation of our algorithm for figuring out save order (it doesn't understand associations to concrete table inheritance base classes). However you should still be able to wrap both SaveChanges calls in a single transaction so that the delete still happens atomically.

Unit test passes, looks good. So far deleting both with a single SaveChanges appears to work.

Thanks.

I appreciate for all your help.

Charles