February 19, 2015 at 12:08Interesting points of Linq to Sql
More than a year has passed since my previous post on a similar topic. During this time, we somehow did not come close to switching to the Entity Framework (according to the current legend, we will switch when a stable version of EF 7 appears), but I have gained some experience that I would like to share. I think that this article will be interesting to those who, like us, still use this generally good, but forgotten Microsoft technology.
Specifying DbType hints (except for enums, more on that below) is not required for entity properties in Linq 2 Sql. And certainly you should not specify the wrong DbType. For example, you should not, if the column is of type nvarchar (50) in the database , indicate Linq 2 Sql that the column is of type nchar (50) . And especially not worth doing this if this field is a discriminator, as in the following example:
Well, let's try to read an entity of type OperationStep from the database and see if Inheritance Mapping can handle it.
Expected no.
The Type property at first glance contains the correct value, but the type of the entity is not defined correctly. What expects Inheritance Mapping to see in the field in order to correctly match the type? Let's try OfType :
And the SQL generated by the linq provider:
The value of the parameter, in principle, was expected, but to detect such a bug was not very simple. Be careful. It is clear that the bug has now appeared, but generally speaking, it can remain for a long time in the system, since entities will be correctly created and subtracted from the database. Until you pay attention to the strange tail at the discriminator values or everything starts to fall after some script updating the discriminators.
Now a few words about storing enum in linq to sql entities.
Linq to sql by default (if DbType is not specified) considers that the column type of Enum is Int . Accordingly, it will be impossible to work with the following entity (the Sex field in the directcrm.Customers tablehas type nvarchar (15) ):
When trying to subtract from the base the Customer entity (in which the Sex field is filled with the string “female” ) will fall from System.InvalidCastException without any chance of understanding what exactly could not be brought to anything. When saving a consumer with the specified gender, we will get this request:
What is noteworthy, subtracting such a tuple from the table also will not work - the same silent System.InvalidCastException will fall . So if you store enum'y strings in the database, using linq to sql, do not forget to specify DbType.
By the way, the Entity Framework is not able to store enum in lines, so in the project where we decided to use it, we had to use a hack: an additional getter for each enum field that the enum parsed itself (the enum value is assumed store in a property of type string).
Linq to sql is able to map both the == operator and the object.Equals () call in SQL , but there are some differences in the mapping.
So, the query for the ActionTemplate entity is filtered by the SystemName field :
Nothing unusual. But what if systemName is null ?
Clear business, so we won’t achieve anything good. Let's try object.equals :
Ingenious
If the request is made in a column that allows the absence of a value, then the broadcast will be already expected:
Therefore, apparently, you should try to use not the equality operator, but object.Equals , since it is translated more "qualitatively".
As you know, Linq does not provide an extension method at all for connecting collections with the possibility of no value in one of them. But sometimes when working with linq to sql we need to get a left outer join in sql, for example, and in such situations we use combinations of linq methods, which eventually translate into a left outer join. I know two ways to get left outer join:
The first option:
The second option:
Both options are translated into a completely identical SQL - left outer join with a subquery and a test column (to determine if an entity exists from the right set):
For reference: CustomerActions - consumer actions in the system, CustomerBalanceChanges - his balance changes, by request we get changes in the consumer’s balance with the corresponding actions (or just an action if it was not a balance change action).
Let's complicate the request: now we want to receive not only changes in the balance of consumers, but also their prizes:
Nothing out of the ordinary, just added another left outer join, as expected. But generally speaking, we could build the query in a different way. For example, since we know that for every prize there is definitely a change in balance, we could write like this:
This will result in the following SQL:
Notice that SELECT 1 as [test] disappeared in this SQL to check for the existence of an entity. And this leads to the fact that such a request does not work, but ends with an InvalidOperationException: "NULL cannot be assigned to a member, which is a System.Int32 type that does not allow NULL values." Since linq no longer tracks its test flag, it tries to honestly compose CustomerBalanceChange and CustomerPrize entities from columns whose values are NULL, but it will not be able to write NULL for example to CustomerBalanceChange.Id, which is what the exception text tells us about.
What workarounds for this problem exist? Well, firstly, you can rephrase the request as it was written in the first case. But this is absolutely not a universal solution, because who said that this can always be done. Linq can fall the same way at the first complex request, and you don’t feel like wasting time on rearranging joins. And the second request is semantically different from the first.
Secondly, we could make a request not to entities, but to certain dto, for example like this:
Since CustomerBalanceChangeId and CustomerPrizeId are now nullable, no problem. But such an approach may not suit us, because we may need exactly the entities (which we want to modify, delete, or call functions on them). So there is a straightforward third way of combining, in which the null check will be done on the sql side:
This translates into not so scary sql, as it might seem at first glance:
But, as you see, there is a nuance. The query was not very complicated, but linq to sql still instead of just using [t3]. [Test] in the final selection, drew the CASE ... WHEN construct . There is nothing to worry about until the request is too large. But if you try to combine tables 10 in this way, then the resulting SQL queries can reach several hundred kilobytes! Several hundred kilobytes of CASE ... WHEN statements .
In addition, to constantly use any of the constructions described above for a simple left outer join is somewhat unprofitable, it would be much easier to write the LeftOuterJoin extension method yourself and use it in queries. Here is how such an extension looks with us:
This extension is always translated, but it uses null checking on the sql side. The following usage is intended:
You may notice that the extension method itself uses the Evaluate and ExpandExpressions methods . These are extension methods from our Mindbox.Expressions library . The ExpandExpressions method recursively traverses the entire expression tree on which it was called, recursively replacing Evaluate calls with the expression on which Evaluate was called. The ExpandExpressions method can be called both on Expression objects and on IQueryable, which is sometimes more convenient (for example, if the query is built in several places). The library also has a number of interesting functions for reflective work with code. Perhaps the library will be useful to someone.
UPD. A small remark from a colleague:
Dbtype
Specifying DbType hints (except for enums, more on that below) is not required for entity properties in Linq 2 Sql. And certainly you should not specify the wrong DbType. For example, you should not, if the column is of type nvarchar (50) in the database , indicate Linq 2 Sql that the column is of type nchar (50) . And especially not worth doing this if this field is a discriminator, as in the following example:
[Table(Name = "directcrm.OperationSteps")]
[InheritanceMapping(Code = "", Type = typeof(OperationStep), IsDefault = true)]
// ...
[InheritanceMapping(Code = "ApplySegment", Type = typeof(ApplySegmentOperationStep))]
public class OperationStep : INotifyPropertyChanging, INotifyPropertyChanged, IValidatable
{
// Некоторое количество кода
...
[Column(Storage = "type", DbType = "nchar(50) NOT NULL", CanBeNull = false, IsDiscriminator = true)]
public string Type
{
get
{
return type;
}
set
{
if ((type != value))
{
SendPropertyChanging();
type = value;
SendPropertyChanged();
}
}
}
}
Well, let's try to read an entity of type OperationStep from the database and see if Inheritance Mapping can handle it.
Expected no.
The Type property at first glance contains the correct value, but the type of the entity is not defined correctly. What expects Inheritance Mapping to see in the field in order to correctly match the type? Let's try OfType :
modelContext.Repositories.Get().Items.OfType().FirstOrDefault();
And the SQL generated by the linq provider:
DECLARE @p0 NChar = 'ApplySegment ';
SELECT TOP (1) [t0].[Type], [t0].[SegmentationSystemName], [t0].[SegmentSystemName], [t0].[Id], [t0].[Order], [t0].[OperationStepGroupId], [t0].[OperationStepTypeSystemName], [t0].[IsMarker]
FROM [directcrm].[OperationSteps] AS [t0]
WHERE [t0].[Type] = @p0;
The value of the parameter, in principle, was expected, but to detect such a bug was not very simple. Be careful. It is clear that the bug has now appeared, but generally speaking, it can remain for a long time in the system, since entities will be correctly created and subtracted from the database. Until you pay attention to the strange tail at the discriminator values or everything starts to fall after some script updating the discriminators.
Now a few words about storing enum in linq to sql entities.
Linq to sql by default (if DbType is not specified) considers that the column type of Enum is Int . Accordingly, it will be impossible to work with the following entity (the Sex field in the directcrm.Customers tablehas type nvarchar (15) ):
[Table(Name = "directcrm.Customers")]
public sealed class Customer : INotifyPropertyChanging, INotifyPropertyChanged, IValidatable
{
// Некоторое количество кода
[Column(Storage = "sex", CanBeNull = true)]
public Sex? Sex
{
get { return sex; }
set
{
if (sex != value)
{
SendPropertyChanging();
sex = value;
SendPropertyChanged();
}
}
}
}
When trying to subtract from the base the Customer entity (in which the Sex field is filled with the string “female” ) will fall from System.InvalidCastException without any chance of understanding what exactly could not be brought to anything. When saving a consumer with the specified gender, we will get this request:
DECLARE @p20 Int = 1
INSERT INTO [directcrm].[Customers](..., [Sex], ...)
VALUES (..., @p7, ...)
What is noteworthy, subtracting such a tuple from the table also will not work - the same silent System.InvalidCastException will fall . So if you store enum'y strings in the database, using linq to sql, do not forget to specify DbType.
By the way, the Entity Framework is not able to store enum in lines, so in the project where we decided to use it, we had to use a hack: an additional getter for each enum field that the enum parsed itself (the enum value is assumed store in a property of type string).
Equality test
Linq to sql is able to map both the == operator and the object.Equals () call in SQL , but there are some differences in the mapping.
So, the query for the ActionTemplate entity is filtered by the SystemName field :
var systemName = "SystemName";
var actionTemplate =
modelContext.Repositories.Get()
.GetActionTemplatesIncludingNonRoot()
.FirstOrDefault(at => at.SystemName == systemName);
DECLARE @p0 NVarChar(MAX) = 'SystemName';
SELECT TOP (1) [t0].[Discriminator], [t0].[Id], [t0].[CategoryId], [t0].[Name], [t0].[Type], [t0].[RowVersion], [t0].[SystemName], [t0].[CreationCondition], [t0].[UsageDescription], [t0].[StartDateTimeUtcOverride], [t0].[EndDateTimeUtcOverride], [t0].[DateCreatedUtc], [t0].[DateChangedUtc], [t0].[CreatedById], [t0].[LastChangedById], [t0].[CampaignId], [t0].[MailingTemplateName], [t0].[UseCustomParameters], [t0].[TargetActionTemplateId], [t0].[ParentActionTemplateId], [t0].[IsTransactional], [t0].[MailingStartTime], [t0].[MailingEndTime], [t0].[IgnoreStopLists], [t0].[ReversedActionTemplateId]
FROM [directcrm].[ActionTemplates] AS [t0]
WHERE [t0].[SystemName] = @p0
Nothing unusual. But what if systemName is null ?
DECLARE @p0 NVarChar(MAX) = null;
SELECT TOP (1) [t0].[Discriminator], [t0].[Id], [t0].[CategoryId], [t0].[Name], [t0].[Type], [t0].[RowVersion], [t0].[SystemName], [t0].[CreationCondition], [t0].[UsageDescription], [t0].[StartDateTimeUtcOverride], [t0].[EndDateTimeUtcOverride], [t0].[DateCreatedUtc], [t0].[DateChangedUtc], [t0].[CreatedById], [t0].[LastChangedById], [t0].[CampaignId], [t0].[MailingTemplateName], [t0].[UseCustomParameters], [t0].[TargetActionTemplateId], [t0].[ParentActionTemplateId], [t0].[IsTransactional], [t0].[MailingStartTime], [t0].[MailingEndTime], [t0].[IgnoreStopLists], [t0].[ReversedActionTemplateId]
FROM [directcrm].[ActionTemplates] AS [t0]
WHERE [t0].[SystemName] = @p0
Clear business, so we won’t achieve anything good. Let's try object.equals :
string systemName = null;
var actionTemplate =
modelContext.Repositories.Get()
.GetActionTemplatesIncludingNonRoot()
.FirstOrDefault(at => object.Equals(at.SystemName, systemName));
SELECT TOP (1) [t0].[Discriminator], [t0].[Id], [t0].[CategoryId], [t0].[Name], [t0].[Type], [t0].[RowVersion], [t0].[SystemName], [t0].[CreationCondition], [t0].[UsageDescription], [t0].[StartDateTimeUtcOverride], [t0].[EndDateTimeUtcOverride], [t0].[DateCreatedUtc], [t0].[DateChangedUtc], [t0].[CreatedById], [t0].[LastChangedById], [t0].[CampaignId], [t0].[MailingTemplateName], [t0].[UseCustomParameters], [t0].[TargetActionTemplateId], [t0].[ParentActionTemplateId], [t0].[IsTransactional], [t0].[MailingStartTime], [t0].[MailingEndTime], [t0].[IgnoreStopLists], [t0].[ReversedActionTemplateId]
FROM [directcrm].[ActionTemplates] AS [t0]
WHERE 0 = 1
Ingenious
WHERE 0 = 1tells us that Linq to sql knows that ActionTemplate.SystemName cannot be null , so the query is also useless. This sacred knowledge of Linq to sql was derived from the value ColumnAttribute.CanBeNull. Unfortunately, from DbType he does not know how to understand this.
If the request is made in a column that allows the absence of a value, then the broadcast will be already expected:
SELECT TOP (1) [t0].[Discriminator], [t0].[Id], [t0].[CategoryId], [t0].[Name], [t0].[Type], [t0].[RowVersion], [t0].[SystemName], [t0].[CreationCondition], [t0].[UsageDescription], [t0].[StartDateTimeUtcOverride], [t0].[EndDateTimeUtcOverride], [t0].[DateCreatedUtc], [t0].[DateChangedUtc], [t0].[CreatedById], [t0].[LastChangedById], [t0].[CampaignId], [t0].[MailingTemplateName], [t0].[UseCustomParameters], [t0].[TargetActionTemplateId], [t0].[ParentActionTemplateId], [t0].[IsTransactional], [t0].[MailingStartTime], [t0].[MailingEndTime], [t0].[IgnoreStopLists], [t0].[ReversedActionTemplateId]
FROM [directcrm].[ActionTemplates] AS [t0]
WHERE [t0].[SystemName] IS NULL
Therefore, apparently, you should try to use not the equality operator, but object.Equals , since it is translated more "qualitatively".
LeftOuterJoin
As you know, Linq does not provide an extension method at all for connecting collections with the possibility of no value in one of them. But sometimes when working with linq to sql we need to get a left outer join in sql, for example, and in such situations we use combinations of linq methods, which eventually translate into a left outer join. I know two ways to get left outer join:
The first option:
CustomerActions
.GroupJoin(CustomerBalanceChanges,
ca => ca,
cbch => cbch.CustomerAction,
(ca, cbchs) => cbchs
.DefaultIfEmpty()
.Select(cbch => new { ca, cbch }))
.SelectMany(g => g)
.Dump();
The second option:
CustomerActions
.SelectMany(ca =>
CustomerBalanceChanges
.Where(cbch => cbch.CustomerAction == ca)
.DefaultIfEmpty(),
(ca, cbch) => new { ca, cbch})
.Dump();
Both options are translated into a completely identical SQL - left outer join with a subquery and a test column (to determine if an entity exists from the right set):
SELECT [t0].[Id], [t0].[IsTimeKnown], [t0].[BrandName], [t0].[ActionTemplateId], [t0].[CustomerId], [t0].[StaffId], [t0].[PointOfContactId], [t0].[OriginalCustomerId], [t0].[IsOriginalCustomerIdExact], [t0].[TransactionalId], [t0].[DateTimeUtc], [t0].[CreationDateTimeUtc], [t2].[test], [t2].[Id] AS [Id2], [t2].[ChangeAmount], [t2].[Comments], [t2].[CustomerActionId], [t2].[AdminSiteComments], [t2].[BalanceId]
FROM [directcrm].[CustomerActions] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[Id], [t1].[ChangeAmount], [t1].[Comments], [t1].[CustomerActionId], [t1].[AdminSiteComments], [t1].[BalanceId]
FROM [promo].[CustomerBalanceChanges] AS [t1]
) AS [t2] ON [t0].[Id] = [t2].[CustomerActionId]
For reference: CustomerActions - consumer actions in the system, CustomerBalanceChanges - his balance changes, by request we get changes in the consumer’s balance with the corresponding actions (or just an action if it was not a balance change action).
Let's complicate the request: now we want to receive not only changes in the balance of consumers, but also their prizes:
CustomerActions
.SelectMany(ca =>
CustomerBalanceChanges
.Where(cbch => cbch.CustomerAction == ca)
.DefaultIfEmpty(),
(ca, cbch) => new { ca, cbch})
.SelectMany(cacbch =>
CustomerPrizes
.Where(cp => cacbch.ca == cp.CustomerAction)
.DefaultIfEmpty(),
(cacbch, cp) => new { cacbch.ca, cacbch.cbch, cp})
.Dump();
SELECT [t0].[Id], [t0].[IsTimeKnown], [t0].[BrandName], [t0].[ActionTemplateId], [t0].[CustomerId], [t0].[StaffId], [t0].[PointOfContactId], [t0].[OriginalCustomerId], [t0].[IsOriginalCustomerIdExact], [t0].[TransactionalId], [t0].[DateTimeUtc], [t0].[CreationDateTimeUtc], [t2].[test], [t2].[Id] AS [Id2], [t2].[ChangeAmount], [t2].[Comments], [t2].[CustomerActionId], [t2].[AdminSiteComments], [t2].[BalanceId], [t4].[test] AS [test2], [t4].[Id] AS [Id3], [t4].[PrizeId], [t4].[SaleFactId], [t4].[PromoMechanicsName], [t4].[WonCustomerPrizeId], [t4].[PrizeType], [t4].[Published], [t4].[PromoMechanicsScheduleItemId], [t4].[CustomerActionId] AS [CustomerActionId2]
FROM [directcrm].[CustomerActions] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[Id], [t1].[ChangeAmount], [t1].[Comments], [t1].[CustomerActionId], [t1].[AdminSiteComments], [t1].[BalanceId]
FROM [promo].[CustomerBalanceChanges] AS [t1]
) AS [t2] ON [t2].[CustomerActionId] = [t0].[Id]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t3].[Id], [t3].[PrizeId], [t3].[SaleFactId], [t3].[PromoMechanicsName], [t3].[WonCustomerPrizeId], [t3].[PrizeType], [t3].[Published], [t3].[PromoMechanicsScheduleItemId], [t3].[CustomerActionId]
FROM [promo].[CustomerPrizes] AS [t3]
) AS [t4] ON [t0].[Id] = [t4].[CustomerActionId]
Nothing out of the ordinary, just added another left outer join, as expected. But generally speaking, we could build the query in a different way. For example, since we know that for every prize there is definitely a change in balance, we could write like this:
CustomerActions
.SelectMany(ca =>
CustomerPrizes
.Join(CustomerBalanceChanges,
cp => cp.CustomerAction,
cbch => cbch.CustomerAction,
(cp, cbch) => new { cbch, cp })
.Where(cbchcp => cbchcp.cbch.CustomerAction == ca)
.DefaultIfEmpty(),
(ca, cbchcp) => new { cbchcp.cbch, cbchcp.cp, ca})
.Dump();
This will result in the following SQL:
SELECT [t2].[Id], [t2].[ChangeAmount], [t2].[Comments], [t2].[CustomerActionId], [t2].[AdminSiteComments], [t2].[BalanceId], [t1].[Id] AS [Id2], [t1].[PrizeId], [t1].[SaleFactId], [t1].[PromoMechanicsName], [t1].[WonCustomerPrizeId], [t1].[PrizeType], [t1].[Published], [t1].[PromoMechanicsScheduleItemId], [t1].[CustomerActionId] AS [CustomerActionId2], [t0].[Id] AS [Id3], [t0].[IsTimeKnown], [t0].[BrandName], [t0].[ActionTemplateId], [t0].[CustomerId], [t0].[StaffId], [t0].[PointOfContactId], [t0].[OriginalCustomerId], [t0].[IsOriginalCustomerIdExact], [t0].[TransactionalId], [t0].[DateTimeUtc], [t0].[CreationDateTimeUtc]
FROM [directcrm].[CustomerActions] AS [t0]
LEFT OUTER JOIN ([promo].[CustomerPrizes] AS [t1]
INNER JOIN [promo].[CustomerBalanceChanges] AS [t2] ON [t1].[CustomerActionId] = [t2].[CustomerActionId]) ON [t2].[CustomerActionId] = [t0].[Id]
Notice that SELECT 1 as [test] disappeared in this SQL to check for the existence of an entity. And this leads to the fact that such a request does not work, but ends with an InvalidOperationException: "NULL cannot be assigned to a member, which is a System.Int32 type that does not allow NULL values." Since linq no longer tracks its test flag, it tries to honestly compose CustomerBalanceChange and CustomerPrize entities from columns whose values are NULL, but it will not be able to write NULL for example to CustomerBalanceChange.Id, which is what the exception text tells us about.
What workarounds for this problem exist? Well, firstly, you can rephrase the request as it was written in the first case. But this is absolutely not a universal solution, because who said that this can always be done. Linq can fall the same way at the first complex request, and you don’t feel like wasting time on rearranging joins. And the second request is semantically different from the first.
Secondly, we could make a request not to entities, but to certain dto, for example like this:
CustomerActions
.SelectMany(ca =>
CustomerPrizes
.Join(CustomerBalanceChanges,
cp => cp.CustomerAction,
cbch => cbch.CustomerAction,
(cp, cbch) => new { cbch, cp })
.Where(cbchcp => cbchcp.cbch.CustomerAction == ca)
.DefaultIfEmpty(),
(ca, cbchcp) => new { cbchcp.cbch, cbchcp.cp, ca})
.Select(cacbchcp => new {
CustomerActionId = cacbchcp.ca.Id,
CustomerBalanceChangeId = (int?)cacbchcp.cbch.Id,
CustomerPrizeId = (int?)cacbchcp.cp.Id,
} )
Since CustomerBalanceChangeId and CustomerPrizeId are now nullable, no problem. But such an approach may not suit us, because we may need exactly the entities (which we want to modify, delete, or call functions on them). So there is a straightforward third way of combining, in which the null check will be done on the sql side:
CustomerActions
.SelectMany(ca =>
CustomerPrizes
.Join(CustomerBalanceChanges,
cp => cp.CustomerAction,
cbch => cbch.CustomerAction,
(cp, cbch) => new { cbch, cp })
.Where(cbchcp => cbchcp.cbch.CustomerAction == ca)
.DefaultIfEmpty(),
(ca, cbchcp) => new {
cbch = cbchcp == null ? null : cbchcp.cbch,
cp = cbchcp == null ? null : cbchcp.cp,
ca
})
.Dump();
This translates into not so scary sql, as it might seem at first glance:
SELECT
(CASE
WHEN [t3].[test] IS NULL THEN 1
ELSE 0
END) AS [value], [t3].[Id], [t3].[ChangeAmount], [t3].[Comments], [t3].[CustomerActionId], [t3].[AdminSiteComments], [t3].[BalanceId], [t3].[Id2], [t3].[PrizeId], [t3].[SaleFactId], [t3].[PromoMechanicsName], [t3].[WonCustomerPrizeId], [t3].[PrizeType], [t3].[Published], [t3].[PromoMechanicsScheduleItemId], [t3].[CustomerActionId2], [t0].[Id] AS [Id3], [t0].[IsTimeKnown], [t0].[BrandName], [t0].[ActionTemplateId], [t0].[CustomerId], [t0].[StaffId], [t0].[PointOfContactId], [t0].[OriginalCustomerId], [t0].[IsOriginalCustomerIdExact], [t0].[TransactionalId], [t0].[DateTimeUtc], [t0].[CreationDateTimeUtc]
FROM [directcrm].[CustomerActions] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t2].[Id], [t2].[ChangeAmount], [t2].[Comments], [t2].[CustomerActionId], [t2].[AdminSiteComments], [t2].[BalanceId], [t1].[Id] AS [Id2], [t1].[PrizeId], [t1].[SaleFactId], [t1].[PromoMechanicsName], [t1].[WonCustomerPrizeId], [t1].[PrizeType], [t1].[Published], [t1].[PromoMechanicsScheduleItemId], [t1].[CustomerActionId] AS [CustomerActionId2]
FROM [promo].[CustomerPrizes] AS [t1]
INNER JOIN [promo].[CustomerBalanceChanges] AS [t2] ON [t1].[CustomerActionId] = [t2].[CustomerActionId]
) AS [t3] ON [t3].[CustomerActionId] = [t0].[Id]
But, as you see, there is a nuance. The query was not very complicated, but linq to sql still instead of just using [t3]. [Test] in the final selection, drew the CASE ... WHEN construct . There is nothing to worry about until the request is too large. But if you try to combine tables 10 in this way, then the resulting SQL queries can reach several hundred kilobytes! Several hundred kilobytes of CASE ... WHEN statements .
In addition, to constantly use any of the constructions described above for a simple left outer join is somewhat unprofitable, it would be much easier to write the LeftOuterJoin extension method yourself and use it in queries. Here is how such an extension looks with us:
public static IQueryable LeftOuterJoin(
this IQueryable outerValues, IQueryable innerValues,
Expression> outerKeySelector,
Expression> innerKeySelector,
Expression> fullResultSelector,
Expression> partialResultSelector)
{
Expression, IEnumerable>> resultSelector =
(outerValue, groupedInnerValues) =>
groupedInnerValues.DefaultIfEmpty().Select(
innerValue => Equals(innerValue, default(TInner)) ?
partialResultSelector.Evaluate(outerValue) :
fullResultSelector.Evaluate(outerValue, innerValue));
return outerValues
.GroupJoin(innerValues, outerKeySelector, innerKeySelector, resultSelector.ExpandExpressions())
.SelectMany(result => result);
}
This extension is always translated, but it uses null checking on the sql side. The following usage is intended:
var cbchcas = customerActions
.LeftOuterJoin(
context.Repositories
.Get()
.Items
.Join(context.Repositories
.Get()
.Items,
cbch => cbch.CustomerAction,
cp => cp.CustomerAction,
(cbch, cp) => new { cbch, cp }),
ca => ca,
cbchcp => cbchcp.cbch.CustomerAction,
(ca, cbchcp) => new { ca, cbchcp.cbch, cbchcp.cp },
ca => new { ca, cbch = (CustomerBalanceChange)null, cp = (CustomerPrize)null })
.ToArray();
SELECT
(CASE
WHEN [t3].[test] IS NULL THEN 1
ELSE 0
END) AS [value], [t3].[Id], [t3].[CustomerActionId], [t3].[ChangeAmount], [t3].[Comments], [t3].[AdminSiteComments], [t3].[BalanceId], [t3].[PrizeType], [t3].[Id2], [t3].[PrizeId], [t3].[PromoMechanicsName] AS [PromoMechanicsSystemName], [t3].[Published], [t3].[PromoMechanicsScheduleItemId], [t3].[SaleFactId], [t3].[CustomerActionId2], [t3].[WonCustomerPrizeId], [t0].[Id] AS [Id3], [t0].[DateTimeUtc], [t0].[IsTimeKnown], [t0].[PointOfContactId], [t0].[BrandName] AS [BrandSystemName], [t0].[CreationDateTimeUtc], [t0].[ActionTemplateId], [t0].[CustomerId], [t0].[StaffId], [t0].[OriginalCustomerId], [t0].[IsOriginalCustomerIdExact], [t0].[TransactionalId]
FROM [directcrm].[CustomerActions] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[Id], [t1].[CustomerActionId], [t1].[ChangeAmount], [t1].[Comments], [t1].[AdminSiteComments], [t1].[BalanceId], [t2].[PrizeType], [t2].[Id] AS [Id2], [t2].[PrizeId], [t2].[PromoMechanicsName], [t2].[Published], [t2].[PromoMechanicsScheduleItemId], [t2].[SaleFactId], [t2].[CustomerActionId] AS [CustomerActionId2], [t2].[WonCustomerPrizeId]
FROM [promo].[CustomerBalanceChanges] AS [t1]
INNER JOIN [promo].[CustomerPrizes] AS [t2] ON [t1].[CustomerActionId] = [t2].[CustomerActionId]
) AS [t3] ON [t0].[Id] = [t3].[CustomerActionId]
You may notice that the extension method itself uses the Evaluate and ExpandExpressions methods . These are extension methods from our Mindbox.Expressions library . The ExpandExpressions method recursively traverses the entire expression tree on which it was called, recursively replacing Evaluate calls with the expression on which Evaluate was called. The ExpandExpressions method can be called both on Expression objects and on IQueryable, which is sometimes more convenient (for example, if the query is built in several places). The library also has a number of interesting functions for reflective work with code. Perhaps the library will be useful to someone.
UPD. A small remark from a colleague:
> Therefore, apparently, you should try to use not the equality operator, but object.Equals, since it translates more "qualitatively".
Bad advice. This, of course, will solve the comparison problem with null. But there are side effects:
- you can write a comparison of the values of incompatible types and the compiler will not swear
- if you compare two values, none of which can be pre-computed by .NET (for example, two properties of an entity), you will get bad and slow SQL (or they are equal, or both are null)
- may not work for enum. There are mapping bugs - he does not always understand that the value needs to be converted to a string, and not to int. For the comparison operation, he understands, and for passing the parameter to object.Equals, it seems that we ran into problems