Interesting moments of LINQ to SQL. Again
A month has passed since my previous post , I think it's time to continue. This time we will talk about Inheritance Mapping, well, those who are especially interested in the end of the article will have a surprise.
So, let's begin.
Of course, we store polymorphic entities in our database. For example, there is an essence CustomerOperation, which reflects some operation that can be performed on a consumer. Operations are performed mainly through services, so there is a successor to CustomerServiceOperation, and we also have a mechanism for WebTracking, for which we have WebTrackingOperation. But enough words, better show the code:
Let's try to get some WebTracking operation. And here is the code:
')
All is well. But what will happen if we suddenly forgot to register the type of WebTrackingOperation in the Inheritance Mapping attributes? If we forget to do this, then such a request will be translated to
Smart LINQ to SQL! But the exception would be the best choice, in my humble opinion. Well, if we forgot the type, but we went through the table of these operations and changed the type directly to the database with a script (for example, before all operations were CustomerService, now WebTracking appeared, and some old ones need to be updated). Let's try to subtract the operation with unregistered discriminator from the database:
The expected base type entity is read. What if you try to save such a miracle?
Here is the code:
Nothing terrible happened. The name has changed, the rest remains the same:
Great, let's now try to write WebTrackingOperation into the database (when refactoring, which I described above, the code that creates such entities would necessarily appear). Attempting this will fail with a funny NullReference, proof:
Do not pay attention that the error falls in the mysterious Mindbox.Data.Linq.dll, it falls in the same way in the classic LINQ to SQL. From an error, as you can see, you never see where to look for a problem, so the bug is not very pleasant. Be careful and do not forget to specify all types of polymorphic entities in the attributes of Inheritance Mapping.
And finally, about the funny property of the discriminator. Let's try to create a new entity of the base class CustomerOperation, assign the discriminator yourself and save:
This will generate an insert with the value of the Discriminator = WebTracking field, however, after inserting LINQ to SQL, the discriminator will re-set itself — that is, it will call its setter with an empty string (because this default value for the base type was specified in the Inheritance Mapping attribute):
If this behavior does not suit you, then there is a simple workaround: in the setter of the discriminator, ignore the blank line setting.
LINQ to SQL has one (no, well, of course, never one, of course, but now it's about a specific one) is a very unpleasant moment. Almost always, if the linq request was built in such a way that it cannot be squatted to sql, linq throws an exception when calling the enumerator. The text of such exceptions is known to everyone, it could be something of a type (pulled a couple from the bug tracker): “Member access 'System.DateTime DateTimeUtc' of 'Itc.DirectCrm.Model.CustomerAction' not legal on type 'System.Linq.IQueryable' 1 [Itc.DirectCrm.Model.CustomerAction] ”or“ Method 'Boolean Evaluate [CustomerLotteryTicket, Boolean] (System.Linq.Expressions.Expression`1 [System.Func`2 [Itc.DirectCrm.Promo.CustomerLotteryTicket, System.Boolean ]], Itc.DirectCrm.Promo.CustomerLotteryTicket) "has no supported translation to SQL”. The key word is almost here. Sometimes LINQ to SQL may consider that instead of throwing a similar exception, it is better to subtract more entities into memory, and already have some conversion in memory. This is very sad for several reasons: it is not documented in any way (as far as I know), because of this, OutOfMemory sometimes falls (since the subtracted entities will never leave the context, although the code will look as if you are reading anonymous objects that GC will be quickly assembled), as well as due to bugs with Inheritance Mapping. Actually, let's look at such a bug.
There is the essence of “Action Template”, it reflects various types of actions of people in the system. There are simple templates: a person made an authorization, went to a specific section of the site, won a prize. There are also all sorts of mailing lists that we have implemented through other types of action templates - that is, through inheritance mapping. A piece of code so that everything is good:
The action template with id = 20 is EmailMailingActionTemplate - Email (I checked), let's subtract it:
Perfectly. And now we will try to execute a query query before requesting this action template:
I specifically limited Id to show that the problem is precisely in this query. EffectiveEndDateTimeUtc is not a column, it is just a property in the class, before calling Select was not called AsEnumerable, so in theory linq should throw one of those exceptions, examples of which I cited above. But no. The query is translated into the following sql (just an entity query):
After such a request, the previous one (pulling out a specific template by Id) returns the action template of the base type:
Automatic enumeration, as this result shows, simply does not pay attention to Inheritance Mapping, always creating the base class entities. Fear automatic enumeration!
Comment from IharBury :
You may have noticed that in the part where I talked about problems with the discriminator, I had the following code presented:
As you can guess, all access to the DBMS from the code is through the repository. Each repository has helper methods for getting entities for some features (consumers' repository has GetByName method, etc.), but since it’s not practical to create their own method for each, then each of our repositories has the Items property - it is just iQueryable of the necessary entities. But what are FixedItems on CustomerOperationRepository? The funny thing is that the FixedItems property in the code looks like this:
This is one of the crutches that you have to use when working with LINQ to SQL. In order to explain the problem, it is necessary to describe the situation a little.
CustomerOperationRepository gives access to CustomerOperation entities, but the CustomerOperation entity itself is a campaign element (of our large aggregate, to which quite a lot of other entities are attached). These entities have similar validation, there are many common properties, so that they are inherited from one class and their repositories are also inherited. Also, all campaign elements are inherited from the ICampaignItem interface, and the base class of the repository takes the entity type as the first parameter of the generic:
The problem is that the methods of this class refer to the TCampaignItem fields using the ICampaignItem interface, and there is indeed a known problem that LINQ to SQL does not map properties defined in interfaces. Therefore, a request, for example, of all operations associated with a campaign (Where (item => item.CampaignId == campaign.Id)), falls with InvalidOperationException: MappingOfInterfacesMemberIsNotSupported: ICampaignItem, CampaignId. In this case, adding a seemingly useless IQueryable to the chain Select (item => item) solves all problems in a magical way. You can also use the object.Equals method instead of the ==: Where (item => Equals (item.CampaignId, campaign.Id) operator), such a query is translated without using FixedItems.
Short and clear bug. LINQ to SQL does not support different settings for null and not null in the heirs for the same field. For example:
PerformActionCustomerServiceOperation requires that the operationStepGroupId value is always (this is required by the domain model), but the same operationStepGroupId field in the IdentificationTrackingOperation may be missing. The base class for these two entities is CustomerOperation, in which there is no operationStepGroupId, this field is added separately both in the PerformActionCustomerServiceOperation and in IdentificationTrackingOperation.
This is how it happens. Pay attention to the CanBeNull values ​​and column type in different entities:
Well, fine, and now we will try to read the list of operations from the database. One of these operations is IdentificationTrackingOperation, which has no OperationStepGroupId.
The attempt fails as expected because we get an InvalidOperationException: CannotAssignNull: System.Int32.
How to deal with it? We made it easy - for LINQ, PerformActionCustomerServiceOperation was allowed to have no values ​​for OperationStepGroupId, and in our validation we check this separately. It is important that CanBeNull in all successors has the same value, otherwise you will have LINQ to SQL debug sessions for a couple of hours.
You may have noticed that errors in LINQ in my screenshots fall in an assembly called Mindbox.Data.Linq.dll. Yes, we forked LINQ to SQL. For example, now you can use not only the InheritanceMappingAttribute, but also the void AddInheritance <TRoot, T> (object code) method on Mindbox.Data.Linq.Mapping.MindboxMappingConfiguration, which allows you to register the heirs of entities in different assemblies.
Our fork can be delivered via Nuget: Mindbox.Data.Linq .
Maybe you want to help or take advantage of - good luck with that.
So, let's begin.
Discriminator Issues
Of course, we store polymorphic entities in our database. For example, there is an essence CustomerOperation, which reflects some operation that can be performed on a consumer. Operations are performed mainly through services, so there is a successor to CustomerServiceOperation, and we also have a mechanism for WebTracking, for which we have WebTrackingOperation. But enough words, better show the code:
[Table(Name = "directcrm.CustomerOperations")] [InheritanceMapping(Code = "", Type = typeof(CustomerOperation), IsDefault = true)] [InheritanceMapping(Code = "Service", Type = typeof(CustomerServiceOperation))] [InheritanceMapping(Code = "WebTracking", Type = typeof(WebTrackingOperation))] public class CustomerOperation : CampaignItemBase, ICampaignItem { // - [Column(Storage = "discriminator", CanBeNull = false, IsDiscriminator = true)] public string Discriminator { get { return discriminator; } private set { if (discriminator != value) { SendPropertyChanging(); discriminator = value; SendPropertyChanged(); } } } // - } Let's try to get some WebTracking operation. And here is the code:
modelContext.Repositories.Get<CustomerOperationRepository>() .FixedItems.OfType<WebTrackingOperation>() .FirstOrDefault(); ')
All is well. But what will happen if we suddenly forgot to register the type of WebTrackingOperation in the Inheritance Mapping attributes? If we forget to do this, then such a request will be translated to
SELECT TOP (1) NULL AS [EMPTY] FROM [directcrm].[CustomerOperations] AS [t0] WHERE 0 = 1 Smart LINQ to SQL! But the exception would be the best choice, in my humble opinion. Well, if we forgot the type, but we went through the table of these operations and changed the type directly to the database with a script (for example, before all operations were CustomerService, now WebTracking appeared, and some old ones need to be updated). Let's try to subtract the operation with unregistered discriminator from the database:
var test = modelContext.Repositories.Get<CustomerOperationRepository>().GetBySystemName("Webtrackingtest"); The expected base type entity is read. What if you try to save such a miracle?
Here is the code:
var test = mc.Repositories.Get<CustomerOperationRepository>().GetBySystemName("Webtrackingtest"); test.NormalizeAndSetName("SomeOperationName"); mc.SubmitChanges(); Nothing terrible happened. The name has changed, the rest remains the same:
Great, let's now try to write WebTrackingOperation into the database (when refactoring, which I described above, the code that creates such entities would necessarily appear). Attempting this will fail with a funny NullReference, proof:
Do not pay attention that the error falls in the mysterious Mindbox.Data.Linq.dll, it falls in the same way in the classic LINQ to SQL. From an error, as you can see, you never see where to look for a problem, so the bug is not very pleasant. Be careful and do not forget to specify all types of polymorphic entities in the attributes of Inheritance Mapping.
And finally, about the funny property of the discriminator. Let's try to create a new entity of the base class CustomerOperation, assign the discriminator yourself and save:
var newOp = new CustomerOperation(); newOp.NormalizeAndSetName("TestForHabr"); newOp.NormalizeAndSetSystemName("TestForHabr"); newOp.NormalizeAndSetDescription("TestForHabr"); newOp.Discriminator = "WebTracking"; newOp.Campaign = test.Campaign; mc.Repositories.Get<CustomerOperationRepository>().Add(newOp); mc.SubmitChanges(); This will generate an insert with the value of the Discriminator = WebTracking field, however, after inserting LINQ to SQL, the discriminator will re-set itself — that is, it will call its setter with an empty string (because this default value for the base type was specified in the Inheritance Mapping attribute):
If this behavior does not suit you, then there is a simple workaround: in the setter of the discriminator, ignore the blank line setting.
Unsolicited Enumeration
LINQ to SQL has one (no, well, of course, never one, of course, but now it's about a specific one) is a very unpleasant moment. Almost always, if the linq request was built in such a way that it cannot be squatted to sql, linq throws an exception when calling the enumerator. The text of such exceptions is known to everyone, it could be something of a type (pulled a couple from the bug tracker): “Member access 'System.DateTime DateTimeUtc' of 'Itc.DirectCrm.Model.CustomerAction' not legal on type 'System.Linq.IQueryable' 1 [Itc.DirectCrm.Model.CustomerAction] ”or“ Method 'Boolean Evaluate [CustomerLotteryTicket, Boolean] (System.Linq.Expressions.Expression`1 [System.Func`2 [Itc.DirectCrm.Promo.CustomerLotteryTicket, System.Boolean ]], Itc.DirectCrm.Promo.CustomerLotteryTicket) "has no supported translation to SQL”. The key word is almost here. Sometimes LINQ to SQL may consider that instead of throwing a similar exception, it is better to subtract more entities into memory, and already have some conversion in memory. This is very sad for several reasons: it is not documented in any way (as far as I know), because of this, OutOfMemory sometimes falls (since the subtracted entities will never leave the context, although the code will look as if you are reading anonymous objects that GC will be quickly assembled), as well as due to bugs with Inheritance Mapping. Actually, let's look at such a bug.
There is the essence of “Action Template”, it reflects various types of actions of people in the system. There are simple templates: a person made an authorization, went to a specific section of the site, won a prize. There are also all sorts of mailing lists that we have implemented through other types of action templates - that is, through inheritance mapping. A piece of code so that everything is good:
[Table(Name = "directcrm.ActionTemplates")] [InheritanceMapping(Code = "", Type = typeof(ActionTemplate), IsDefault = true)] [InheritanceMapping(Code = "Hierarchical", Type = typeof(HierarchicalActionTemplate))] [InheritanceMapping(Code = "CustomerToCustomer", Type = typeof(CustomerToCustomerActionTemplate))] [InheritanceMapping(Code = "EmailMailing", Type = typeof(EmailMailingActionTemplate))] [InheritanceMapping(Code = "SmsMailing", Type = typeof(SmsMailingActionTemplate))] [InheritanceMapping(Code = "BannerCampaign", Type = typeof(BannerCampaignActionTemplate))] public class ActionTemplate : INotifyPropertyChanging, INotifyPropertyChanged, IValidatable, IEntityWithSystemName { // - } The action template with id = 20 is EmailMailingActionTemplate - Email (I checked), let's subtract it:
var actualTemplate = modelContext.Repositories.Get<ActionTemplateRepository>().GetById(20); Perfectly. And now we will try to execute a query query before requesting this action template:
var test = modelContext.Repositories.Get<ActionTemplateRepository>() .Items .Where(at => at.Id == 20) .Select(at => at.EffectiveEndDateTimeUtc) .FirstOrDefault(); I specifically limited Id to show that the problem is precisely in this query. EffectiveEndDateTimeUtc is not a column, it is just a property in the class, before calling Select was not called AsEnumerable, so in theory linq should throw one of those exceptions, examples of which I cited above. But no. The query is translated into the following sql (just an entity query):
SELECT TOP (1) [t0].[Id], [t0].[CategoryId], [t0].[Name], [t0].[Type], [t0].[Discriminator], [t0].[RowVersion], [t0].[SystemName], [t0].[CreationCondition], [t0].[UsageDescription], [t0].[StartDateTimeUtcOverride], [t0].[EndDateTimeUtcOverride], [t0].[DateCreatedUtc], [t0].[DateChangedUtc], [t0].[CreatedById], [t0].[LastChangedById], [t0].[CampaignId] FROM [directcrm].[ActionTemplates] AS [t0] WHERE [t0].[Id] = @p0 After such a request, the previous one (pulling out a specific template by Id) returns the action template of the base type:
Automatic enumeration, as this result shows, simply does not pay attention to Inheritance Mapping, always creating the base class entities. Fear automatic enumeration!
Comment from IharBury :
I'm afraid this doesn’t quite reflect what is really going on. LINQ does not treat code like AsEnumerable. It performs mapping for mapping and the rest is in memory. For example, if you make not just the Select properties of an entity, but the Select properties of a related entity, then only a related entity will be created in memory.
It would be a good advice not to make properties for entities that do not use SQL - to make them methods so that the problem is immediately visible.
Fixeditems
You may have noticed that in the part where I talked about problems with the discriminator, I had the following code presented:
modelContext.Repositories.Get<CustomerOperationRepository>() .FixedItems.OfType<WebTrackingOperation>() .FirstOrDefault(); As you can guess, all access to the DBMS from the code is through the repository. Each repository has helper methods for getting entities for some features (consumers' repository has GetByName method, etc.), but since it’s not practical to create their own method for each, then each of our repositories has the Items property - it is just iQueryable of the necessary entities. But what are FixedItems on CustomerOperationRepository? The funny thing is that the FixedItems property in the code looks like this:
public IQueryable<TCampaignItem> FixedItems { get { return base.Items.Select(item => item); } } This is one of the crutches that you have to use when working with LINQ to SQL. In order to explain the problem, it is necessary to describe the situation a little.
CustomerOperationRepository gives access to CustomerOperation entities, but the CustomerOperation entity itself is a campaign element (of our large aggregate, to which quite a lot of other entities are attached). These entities have similar validation, there are many common properties, so that they are inherited from one class and their repositories are also inherited. Also, all campaign elements are inherited from the ICampaignItem interface, and the base class of the repository takes the entity type as the first parameter of the generic:
public abstract class CampaignItemRepositoryBase<TCampaignItem, TInitialState> : ChangeRestrictedExtensionSubsetRepository<TCampaignItem, int, Campaign>, ICampaignItemRepository, ICampaignRelatedItemRepository<TCampaignItem> where TCampaignItem : class, ICampaignItem, new() where TInitialState : CampaignItemInitialState The problem is that the methods of this class refer to the TCampaignItem fields using the ICampaignItem interface, and there is indeed a known problem that LINQ to SQL does not map properties defined in interfaces. Therefore, a request, for example, of all operations associated with a campaign (Where (item => item.CampaignId == campaign.Id)), falls with InvalidOperationException: MappingOfInterfacesMemberIsNotSupported: ICampaignItem, CampaignId. In this case, adding a seemingly useless IQueryable to the chain Select (item => item) solves all problems in a magical way. You can also use the object.Equals method instead of the ==: Where (item => Equals (item.CampaignId, campaign.Id) operator), such a query is translated without using FixedItems.
Null / Not null in different heirs
Short and clear bug. LINQ to SQL does not support different settings for null and not null in the heirs for the same field. For example:
[Table(Name = "directcrm.CustomerOperations")] [InheritanceMapping(Code = "", Type = typeof(CustomerOperation), IsDefault = true)] [InheritanceMapping(Code = "Service", Type = typeof(CustomerServiceOperation))] [InheritanceMapping(Code = "Custom", Type = typeof(CustomCustomerServiceOperation))] [InheritanceMapping(Code = "PerformAction", Type = typeof(PerformActionCustomerServiceOperation))] [InheritanceMapping(Code = "WebTracking", Type = typeof(WebTrackingOperation))] [InheritanceMapping(Code = "IdentificationTracking", Type = typeof(IdentificationTrackingOperation))] [InheritanceMapping(Code = "CustomerOperationByStaff", Type = typeof(CustomerOperationByStaff))] public class CustomerOperation : CampaignItemBase, ICampaignItem PerformActionCustomerServiceOperation requires that the operationStepGroupId value is always (this is required by the domain model), but the same operationStepGroupId field in the IdentificationTrackingOperation may be missing. The base class for these two entities is CustomerOperation, in which there is no operationStepGroupId, this field is added separately both in the PerformActionCustomerServiceOperation and in IdentificationTrackingOperation.
This is how it happens. Pay attention to the CanBeNull values ​​and column type in different entities:
public class IdentificationTrackingOperation : CustomerOperation { private int? operationStepGroupId; [Column(Storage = "operationStepGroupId", CanBeNull = true)] public int? OperationStepGroupId { get { return operationStepGroupId; } set { if (operationStepGroupId != value) { SendPropertyChanging(); operationStepGroupId = value; SendPropertyChanged(); } } } } public class PerformActionCustomerServiceOperation : CustomerServiceOperation, IPerformActionCustomerServiceOperation { private int operationStepGroupId; private EntityRef<OperationStepGroup> operationStepGroup; [Column(Storage = "operationStepGroupId", CanBeNull = false)] public int OperationStepGroupId { get { return operationStepGroupId; } set { if (operationStepGroupId != value) { SendPropertyChanging(); operationStepGroupId = value; SendPropertyChanged(); } } } } Well, fine, and now we will try to read the list of operations from the database. One of these operations is IdentificationTrackingOperation, which has no OperationStepGroupId.
The attempt fails as expected because we get an InvalidOperationException: CannotAssignNull: System.Int32.
How to deal with it? We made it easy - for LINQ, PerformActionCustomerServiceOperation was allowed to have no values ​​for OperationStepGroupId, and in our validation we check this separately. It is important that CanBeNull in all successors has the same value, otherwise you will have LINQ to SQL debug sessions for a couple of hours.
Those who read a surprise
You may have noticed that errors in LINQ in my screenshots fall in an assembly called Mindbox.Data.Linq.dll. Yes, we forked LINQ to SQL. For example, now you can use not only the InheritanceMappingAttribute, but also the void AddInheritance <TRoot, T> (object code) method on Mindbox.Data.Linq.Mapping.MindboxMappingConfiguration, which allows you to register the heirs of entities in different assemblies.
Our fork can be delivered via Nuget: Mindbox.Data.Linq .
Maybe you want to help or take advantage of - good luck with that.
Source: https://habr.com/ru/post/253779/
All Articles