Интересные моменты работы Linq to Sql
Прошло уже больше года с моего предыдущего поста на похожую тему. За это время мы как-то не приблизились к переходу на Entity Framework (по текущей легенде, мы перейдём, когда появится стабильная версия EF 7), ну, а у меня накопилось некоторое количество опыта, которым я бы хотел поделиться. Думаю, что эта статья будет интересна тем, кто, как и мы, до сих пор пользуются этой в общем-то неплохой, но позабытой Microsoft технологией.DbTypeУказание подсказки DbType (за исключением enum’ов, об этом ниже) не является обязательным для свойств сущностей в Linq 2 Sql. И уж точно не стоит указывать неправильный DbType. Например, не стоит, если в базе колонка имеет тип nvarchar (50), указывать Linq 2 Sql, что колонка имеет тип nchar (50). И особенно не стоит так делать, если это поле является дискриминатором, как в следующем примере: [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 ();
}
}
}
}
Ну что ж, попробуем прочитать из базы сущность типа OperationStep и посмотреть, справится ли Inheritance Mapping.
Ожидаемо, нет.
Свойство Type на первый взгляд содержит верное значение, но тип сущности определён неверно. Что же ожидает Inharitance Mapping увидеть в поле для того, чтобы правильно сопоставить тип? Попробуем OfType:
modelContext.Repositories.Get
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; Значение параметра, в принципе, было ожидаемо, но обнаружить такой баг было не очень просто. Будьте внимательнее. Понятное дело, что сейчас баг проявился, но вообще говоря, он может оставаться длительное время в системе, так как сущности будут правильно создаваться и вычитываться из базы. До тех пор, пока вы не обратите внимание на странный хвост у значений дискриминатора или всё не начнёт валиться после какого-нибудь скрипта, обновляющего дискриминаторы.
Теперь пара слов о хранении enum’ов в сущностях linq to sql.
Linq to sql по-умолчанию (если DbType не указан) считает, что тип колонки у Enum’а — Int. Соответственно работать со следующей сущностью будет невозможно (поле Sex в таблице directcrm.Customers имеет тип 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 (); } } } } При попытке вычитать из базы сущность Customer (в которой поле Sex заполнено строкой «female») будет падать с System.InvalidCastException без каких-то шансов понять, что именно не удалось к чему привести. При сохранении потребителя с указанным полом мы получим вот такой запрос:
DECLARE @p20 Int = 1
INSERT INTO [directcrm].[Customers](…, [Sex], …) VALUES (…, @p7, …) Что примечательно, вычитать такой кортеж из таблицы так же не выйдет — упадёт всё тот же молчаливый System.InvalidCastException. Так что если храните enum’ы строками в базе, используя linq to sql, не забывайте указывать DbType.К слову сказать, Entity Framework не в состоянии хранить enum’ы в строках, поэтому в проекте, где мы решили его использовать, пришлось использовать хак: дополнительный getter для каждого enum-поля, который сам парсил enum (при этом значение enum’а предполагается хранить в свойстве типа string).
Проверка на равенство Linq to sql в состоянии смаппить в SQL как оператор ==, так и вызов object.Equals (), однако в маппинге наблюдаются некоторые отличия.Итак, запрос сущности ActionTemplate с фильтрацией по полю SystemName:
var systemName = «SystemName»;
var actionTemplate =
modelContext.Repositories.Get
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 Ничего необычного. Но вдруг systemName будет иметь значение 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 Ясное дело, таким образом мы ничего хорошего не добьёмся. Попробуем object.equals:
string systemName = null;
var actionTemplate =
modelContext.Repositories.Get
WHERE 0 = 1
подсказывает нам, что Linq to sql знает, что ActionTemplate.SystemName не может быть null, поэтому и запрос такой бесполезен. Это сакральное знание Linq to sql получил из значения ColumnAttribute.CanBeNull. К сожалению, из DbType он не умеет это понимать.Если же запрос делается по колонке, допускающей отсутствие значения, то трансляция будет уже ожидаемой: 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 Поэтому видимо нужно стараться использовать не оператор равенства, а object.Equals, так как он транслируется более «качественно».
LeftOuterJoin Как известно, Linq вообще не предоставляет extension-метода для соединения коллекций с возможностью отсутствия значения в одной из них. Но иногда при работе с linq to sql нам требуется получить в sql left outer join, к примеру, и в таких ситуациях мы используем комбинации методов linq, которые в итоге транслируются в left outer join. Мне известны два способа получить left outer join: Первый вариант: CustomerActions .GroupJoin (CustomerBalanceChanges, ca => ca, cbch => cbch.CustomerAction, (ca, cbchs) => cbchs .DefaultIfEmpty () .Select (cbch => new { ca, cbch })) .SelectMany (g => g) .Dump (); Второй вариант:
CustomerActions .SelectMany (ca => CustomerBalanceChanges .Where (cbch => cbch.CustomerAction == ca) .DefaultIfEmpty (), (ca, cbch) => new { ca, cbch}) .Dump (); Оба варианта транслируются в совершенно идентичный SQL — left outer join с подзапросом и test-колонкой (для определения, существует ли сущность из правого множества):
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] Для справки: CustomerActions — действия потребителя в системе, CustomerBalanceChanges — его изменения баланса, запросом мы получаем изменения баланса потребителя с соответствующими действиями (либо просто действие, если это было не действие изменения баланса).
Усложним запрос: теперь мы хотим получать не только изменения баланса потребителей, но ещё и их призы:
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] Ничего необычного, просто добавился ещё один left outer join, как и ожидалось. Но вообще говоря, мы могли бы построить запрос и по-другому. Например, так как мы знаем, что для каждого приза точно есть изменение баланса, можно было бы написать вот так:
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 (); Это приведёт к вот такому 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] Заметьте, в этом SQL исчез SELECT 1 as [test] для проверки наличия сущности. И это приводит к тому, что такой запрос не работает, а завершается с InvalidOperationException: «Значение NULL не может быть присвоено члену, который является типом System.Int32, не допускающим значения NULL.». Так как linq больше не отслеживает свой test-флаг, он пытается честно составить сущности CustomerBalanceChange и CustomerPrize из колонок, значения которых NULL, но он не сможет записать NULL например в CustomerBalanceChange.Id, о чём и сообщает нам текст exception’а.Какие методы обхода этой проблемы существуют? Ну, во-первых, можно перефразировать запрос так, как он был написан в первом случае. Но это совершенно не универсальное решение, ведь кто сказал, что так можно сделать всегда. Linq при первом же сложном запросе может упасть так же, и тратить время на перестановку join’ов совершенно не хочется. Да и второй запрос семантически отличается от первого.Во-вторых, мы могли бы делать запрос не к сущностям, а к неким dto, например вот так:
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, }) Так как CustomerBalanceChangeId и CustomerPrizeId теперь nullable, проблем не возникает. Но нас может не устраивать такой подход, ведь нам могут быть нужны именно сущности (которые мы хотим изменять, удалять или вызывать функции на них). Так что есть прямолинейный третий способ объединения, в котором проверка на null будет производится на стороне sql:
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 (); Это транслируется не в такой уж и страшный sql, как может показаться с первого взгляда:
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] Но, как вы видите, есть нюанс. Запрос был не очень сложный, но linq to sql всё равно вместо того, чтобы просто использовать [t3].[test] в конечной выборке, нарисовал конструкцию CASE… WHEN. В этом нет ничего страшного, пока запрос не стал слишком большим. Но если таким образом попробовать объединить таблиц 10, то итоговые запросы на SQL могут достигать нескольких сотен килобайт! Несколько сотен килобайт операторов CASE… WHEN.
Кроме того, постоянно использовать для простого left outer join’а любую из описанных выше конструкций несколько накладно, на много легче было бы самому написать extension-метод LeftOuterJoin и использовать его в запросах. Вот как такой extension выглядит у нас:
public static IQueryable
return outerValues .GroupJoin (innerValues, outerKeySelector, innerKeySelector, resultSelector.ExpandExpressions ()) .SelectMany (result => result); } Этот extension транслируется всегда, но в нём используется проверка на null на стороне sql. Предполагается следующее использование:
var cbchcas = customerActions
.LeftOuterJoin (
context.Repositories
.Get
