March 23, 2015 at 14:58Interesting points in LINQ to SQL. Again
A month has passed from my previous post , in my opinion it's time to continue. This time we’ll talk about Inheritance Mapping, but for those who are especially interested, there will be a surprise at the end of the article.
So, let's begin.
Of course, we store polymorphic entities in our database. For example, there is a CustomerOperation entity that reflects some operation that can be performed on a consumer. Operations are performed mainly through services, therefore there is a successor to CustomerServiceOperation, and we also have a WebTracking mechanism, for which there is a WebTrackingOperation. But enough words, I'd better show the code:
Let's try to get some kind of WebTracking operation. And here is the code:
Things are good. But what happens if we suddenly forget to register the WebTrackingOperation type in the Inheritance Mapping attributes? If we forgot to do this, then such a request will be transmitted to
Clever LINQ to SQL! But an exception would be the best choice, in my humble opinion. Well, if we forgot to register the type, but went through the table of these operations and changed the type to some script directly in the database (for example, earlier all operations were CustomerService, and now WebTracking has appeared, and some old ones need to be updated). Let's try to subtract the operation with an unregistered discriminator from the database:
The entity of the base type is expected to be subtracted. 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:
Well, let's now try to write WebTrackingOperation to the database (when refactoring, which I described above, the code that creates such entities would certainly appear). This attempt will fail with a funny NullReference, proof:
For now, ignore the error in the mysterious Mindbox.Data.Linq.dll, it falls in the same way in the classic LINQ to SQL. From the error, as you can see, it’s never seen at all where to look for the 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 fun property of the discriminator. Let's try to create a new CustomerOperation base class entity, assign the discriminator yourself, and save:
In this case, insert will be generated with the value of the Discriminator = WebTracking field, however, after inserting LINQ to SQL, the discriminator will re-display the discriminator 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, that is, a simple workaround: in the discriminator setter, ignore the empty line.
LINQ to SQL has one (no, well, of course, never one, but now it’s a concrete) very unpleasant moment. Almost always, if the linq query was built in such a way that it cannot be mapped onto sql, linq will throw an exception when the enumerator is called. The text of such exceptions is known to everyone, it can be something like (pulled a couple out of the bugtracker): “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 keyword here is almost. Sometimes LINQ to SQL may consider that instead of throwing such an exception, it is better to subtract more entities into memory, and already make some transformations in memory. This is very sad for several reasons: it is not documented in any way (as far as I know), OutOfMemory sometimes falls due to this (since the read entities will never leave the context, although the code will look like you are reading anonymous objects that will be quickly collected by GC), as well as due to bugs with Inheritance Mapping. Actually, let's look at such a bug.
There is an entity “Action Template”, it reflects various types of actions of people in the system. There are simple templates: a person logged in, went to a specific section of the site, won a prize. There are also all kinds of mailings that we implement through other types of action templates - that is, through inheritance mapping. A piece of code so that everything is fine:
The action template with id = 20 is EmailMailingActionTemplate - Email newsletter (I checked), let's subtract it:
Perfectly. Now, let's try before requesting this action template to execute a tabbed request:
I specially limited Id to show that the problem is in this request. EffectiveEndDateTimeUtc is not a column, it's just a property in the class, AsEnumerable was not called before calling Select, so in theory linq should throw one of the very exceptions, examples of which I cited above. But no. The query translates to the following sql (just an entity query):
After such a request, the previous one (pulling out a specific template by Id) returns a basic type action template:
Automatic enumeration, as this result shows, simply does not pay attention to Inheritance Mapping, always creating base class entities. Be careful not to auto-enumerate!
Comment by IharBury :
You may have noticed that in the part where I talked about problems with the discriminator, I had the following code:
As you might guess, all access to the DBMS from the code is through repositories. Each repository has helpers methods for obtaining entities according to some criteria (the consumer repository has a GetByName method, etc.), but since it’s not practical to create a method for everyone, each of our repositories has the Items property - it's just the iQueryable of the required entities. But what exactly is FixedItems on CustomerOperationRepository? The funny thing is that the FixedItems property in the code looks like this:
This is one of the crutches you have to use when working with LINQ to SQL. In order to explain the problem, you have to describe the situation a bit.
CustomerOperationRepository gives access to CustomerOperation entities, but the CustomerOperation entity itself is an element of the campaign (our large aggregate, to which quite a few other entities are attached). These entities have similar validation, they have many common properties, so they inherit from one class and their repositories also inherit. All campaign elements are also inherited from the ICampaignItem interface, and the base class of the repository accepts the entity type as the first generic parameter:
The problem is that in the methods of this class, the TCampaignItem fields are accessed using the ICampaignItem interface, and there really is a known problem that LINQ to SQL does not map the properties defined in the interfaces. Therefore, the request, for example, of all operations associated with the campaign (Where (item => item.CampaignId == campaign.Id)) falls from InvalidOperationException: MappingOfInterfacesMemberIsNotSupported: ICampaignItem, CampaignId. At the same time, adding to the IQueryable chain the seemingly useless Select (item => item) magically solves all problems. You can also use the object.Equals method instead of the ==: Where operator (item => Equals (item.CampaignId, campaign.Id)), such a request is broadcast without using FixedItems.
Short and clear bug. LINQ to SQL does not support different null and not null settings for successors in the same field. For example:
PerformActionCustomerServiceOperation requires that the value of operationStepGroupId always be (as required by the domain model), but the same field operationStepGroupId in IdentificationTrackingOperation may not be present. The base class for these two entities is CustomerOperation, in which there is no operationStepGroupId, this field is added separately to PerformActionCustomerServiceOperation and IdentificationTrackingOperation.
Here's how it goes. Pay attention to the CanBeNull values and the column type in different entities:
Well, fine, and now let's try to read the list of operations from the database. One of these operations is IdentificationTrackingOperation, which does not have an OperationStepGroupId.
The attempt fails as expected, because we get InvalidOperationException: CannotAssignNull: System.Int32.
How to deal with this? We did it simply - for LINQ, we allowed PerformActionCustomerServiceOperation to have no values for OperationStepGroupId, and we check this separately in our validation. It is important that CanBeNull has the same value in all descendants, otherwise you will have LINQ to SQL debug sessions for a couple of hours.
You may have noticed that the 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 InheritanceMappingAttribute, but also the void AddInheritance method to register inheritors of entities(object code) 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 something or take advantage - good luck with that.
So, let's begin.
Discriminator Issues
Of course, we store polymorphic entities in our database. For example, there is a CustomerOperation entity that reflects some operation that can be performed on a consumer. Operations are performed mainly through services, therefore there is a successor to CustomerServiceOperation, and we also have a WebTracking mechanism, for which there is a WebTrackingOperation. But enough words, I'd 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 kind of WebTracking operation. And here is the code:
modelContext.Repositories.Get()
.FixedItems.OfType()
.FirstOrDefault();
Things are good. But what happens if we suddenly forget to register the WebTrackingOperation type in the Inheritance Mapping attributes? If we forgot to do this, then such a request will be transmitted to
SELECT TOP (1) NULL AS [EMPTY]
FROM [directcrm].[CustomerOperations] AS [t0]
WHERE 0 = 1
Clever LINQ to SQL! But an exception would be the best choice, in my humble opinion. Well, if we forgot to register the type, but went through the table of these operations and changed the type to some script directly in the database (for example, earlier all operations were CustomerService, and now WebTracking has appeared, and some old ones need to be updated). Let's try to subtract the operation with an unregistered discriminator from the database:
var test = modelContext.Repositories.Get().GetBySystemName("Webtrackingtest");
The entity of the base type is expected to be subtracted. What if you try to save such a miracle?
Here is the code:
var test = mc.Repositories.Get().GetBySystemName("Webtrackingtest");
test.NormalizeAndSetName("SomeOperationName");
mc.SubmitChanges();
Nothing terrible happened. The name has changed, the rest remains the same:
Well, let's now try to write WebTrackingOperation to the database (when refactoring, which I described above, the code that creates such entities would certainly appear). This attempt will fail with a funny NullReference, proof:
For now, ignore the error in the mysterious Mindbox.Data.Linq.dll, it falls in the same way in the classic LINQ to SQL. From the error, as you can see, it’s never seen at all where to look for the 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 fun property of the discriminator. Let's try to create a new CustomerOperation base class entity, 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().Add(newOp);
mc.SubmitChanges();
In this case, insert will be generated with the value of the Discriminator = WebTracking field, however, after inserting LINQ to SQL, the discriminator will re-display the discriminator 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, that is, a simple workaround: in the discriminator setter, ignore the empty line.
Unsolicited enumeration
LINQ to SQL has one (no, well, of course, never one, but now it’s a concrete) very unpleasant moment. Almost always, if the linq query was built in such a way that it cannot be mapped onto sql, linq will throw an exception when the enumerator is called. The text of such exceptions is known to everyone, it can be something like (pulled a couple out of the bugtracker): “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 keyword here is almost. Sometimes LINQ to SQL may consider that instead of throwing such an exception, it is better to subtract more entities into memory, and already make some transformations in memory. This is very sad for several reasons: it is not documented in any way (as far as I know), OutOfMemory sometimes falls due to this (since the read entities will never leave the context, although the code will look like you are reading anonymous objects that will be quickly collected by GC), as well as due to bugs with Inheritance Mapping. Actually, let's look at such a bug.
There is an entity “Action Template”, it reflects various types of actions of people in the system. There are simple templates: a person logged in, went to a specific section of the site, won a prize. There are also all kinds of mailings that we implement through other types of action templates - that is, through inheritance mapping. A piece of code so that everything is fine:
[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 newsletter (I checked), let's subtract it:
var actualTemplate = modelContext.Repositories.Get().GetById(20);
Perfectly. Now, let's try before requesting this action template to execute a tabbed request:
var test = modelContext.Repositories.Get()
.Items
.Where(at => at.Id == 20)
.Select(at => at.EffectiveEndDateTimeUtc)
.FirstOrDefault();
I specially limited Id to show that the problem is in this request. EffectiveEndDateTimeUtc is not a column, it's just a property in the class, AsEnumerable was not called before calling Select, so in theory linq should throw one of the very exceptions, examples of which I cited above. But no. The query translates to 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 a basic type action template:
Automatic enumeration, as this result shows, simply does not pay attention to Inheritance Mapping, always creating base class entities. Be careful not to auto-enumerate!
Comment by IharBury :
I'm afraid this does not completely reflect what is actually happening. LINQ does not interpret code like AsEnumerable. It performs mapping in order to map, and the rest it performs in memory. For example, if you make not just Select properties of an entity, but Select properties of a related entity, then only the related entity will be created in memory.
It would be good advice not to make properties on entities that do not capitalize on 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:
modelContext.Repositories.Get()
.FixedItems.OfType()
.FirstOrDefault();
As you might guess, all access to the DBMS from the code is through repositories. Each repository has helpers methods for obtaining entities according to some criteria (the consumer repository has a GetByName method, etc.), but since it’s not practical to create a method for everyone, each of our repositories has the Items property - it's just the iQueryable of the required entities. But what exactly is FixedItems on CustomerOperationRepository? The funny thing is that the FixedItems property in the code looks like this:
public IQueryable FixedItems
{
get
{
return base.Items.Select(item => item);
}
}
This is one of the crutches you have to use when working with LINQ to SQL. In order to explain the problem, you have to describe the situation a bit.
CustomerOperationRepository gives access to CustomerOperation entities, but the CustomerOperation entity itself is an element of the campaign (our large aggregate, to which quite a few other entities are attached). These entities have similar validation, they have many common properties, so they inherit from one class and their repositories also inherit. All campaign elements are also inherited from the ICampaignItem interface, and the base class of the repository accepts the entity type as the first generic parameter:
public abstract class CampaignItemRepositoryBase
: ChangeRestrictedExtensionSubsetRepository,
ICampaignItemRepository,
ICampaignRelatedItemRepository
where TCampaignItem : class, ICampaignItem, new()
where TInitialState : CampaignItemInitialState
The problem is that in the methods of this class, the TCampaignItem fields are accessed using the ICampaignItem interface, and there really is a known problem that LINQ to SQL does not map the properties defined in the interfaces. Therefore, the request, for example, of all operations associated with the campaign (Where (item => item.CampaignId == campaign.Id)) falls from InvalidOperationException: MappingOfInterfacesMemberIsNotSupported: ICampaignItem, CampaignId. At the same time, adding to the IQueryable chain the seemingly useless Select (item => item) magically solves all problems. You can also use the object.Equals method instead of the ==: Where operator (item => Equals (item.CampaignId, campaign.Id)), such a request is broadcast without using FixedItems.
Null / Not null in different heirs
Short and clear bug. LINQ to SQL does not support different null and not null settings for successors in 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 value of operationStepGroupId always be (as required by the domain model), but the same field operationStepGroupId in IdentificationTrackingOperation may not be present. The base class for these two entities is CustomerOperation, in which there is no operationStepGroupId, this field is added separately to PerformActionCustomerServiceOperation and IdentificationTrackingOperation.
Here's how it goes. Pay attention to the CanBeNull values and the 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;
[Column(Storage = "operationStepGroupId", CanBeNull = false)]
public int OperationStepGroupId
{
get { return operationStepGroupId; }
set
{
if (operationStepGroupId != value)
{
SendPropertyChanging();
operationStepGroupId = value;
SendPropertyChanged();
}
}
}
}
Well, fine, and now let's try to read the list of operations from the database. One of these operations is IdentificationTrackingOperation, which does not have an OperationStepGroupId.
The attempt fails as expected, because we get InvalidOperationException: CannotAssignNull: System.Int32.
How to deal with this? We did it simply - for LINQ, we allowed PerformActionCustomerServiceOperation to have no values for OperationStepGroupId, and we check this separately in our validation. It is important that CanBeNull has the same value in all descendants, otherwise you will have LINQ to SQL debug sessions for a couple of hours.
For those who have read, a surprise
You may have noticed that the 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 InheritanceMappingAttribute, but also the void AddInheritance method to register inheritors of entities
Our fork can be delivered via Nuget: Mindbox.Data.Linq .
Maybe you want to help something or take advantage - good luck with that.