Данная статья является продолжением
Сразу приведу интересные ссылки
Интересные ссылки
http://www.dotnet-tricks.com/Tutorial/linq/UXPF181012-SQL-Joins-with-C
https://www.linqpad.net/WhyLINQBeatsSQL.aspx
http://metanit.com/sharp/entityframework/4.6.php
http://habrahabr.ru/post/203214/
Приведу несколько примеров левого соединения с условием неравенства.
Суть запроса в левом соединении Номенкладуры и подчиненных единиц при условии, что Штрих-код начинается с «4»
var qr = from Номенклатура in бд.Спр_Номенклатура
from единицы in бд.Спр_Единицы.Where(единица => единица.ВладелецId == Номенклатура.ID && единица.ШтрихКод.CompareTo("4") > 0).DefaultIfEmpty()
select new
{
Номенклатура.Наименование,
Номенклатура.ПолнНаименование,
единицы.ШтрихКод,
ОКЕИ = единицы.ОКЕИ.Наименование
};
Генерирует такой запрос
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[DESCR] AS [DESCR],
[Limit1].[SP101] AS [SP101],
[Limit1].[SP80] AS [SP80],
[Limit1].[DESCR1] AS [DESCR1]
FROM ( SELECT TOP (1000)
[Extent1].[DESCR] AS [DESCR],
[Extent1].[SP101] AS [SP101],
[Extent2].[SP80] AS [SP80],
[Extent3].[DESCR] AS [DESCR1],
1 AS [C1]
FROM [dbo].[SC84] AS [Extent1]
LEFT OUTER JOIN [dbo].[SC75] AS [Extent2] ON ([Extent2].[PARENTEXT] = [Extent1].[ID]) AND([Extent2].[SP80] > N'4')
LEFT OUTER JOIN [dbo].[SC41] AS [Extent3] ON [Extent2].[SP79] = [Extent3].[ID]
) AS [Limit1]
Доступ к запросу как к стандартной плоской таблице
foreach (var элем in qr.Take(1000))
{
if (элем.ШтрихКод == null)
continue;
Console.WriteLine("{0}.{1} - {2}", элем.Наименование.TrimEnd(), элем.ШтрихКод == null ? "null" : элем.ШтрихКод, элем.ПолнНаименование.TrimEnd(), элем.ОКЕИ == null ? "null" :элем.ОКЕИ);
}
Этот же запрос можно представить в более понятной форме в виде дерева
var qr = from Номенклатура in бд.Спр_Номенклатура
select new
{
Номенклатура.Наименование,
Номенклатура.ПолнНаименование,
Единицы = (Номенклатура.ПодчиненныеЕдиницы.Where(единица => единица.ШтрихКод.CompareTo("4") > 0)
.Select(единица =>
new
{
единица.ШтрихКод,
ОКЕИ = единица.ОКЕИ.Наименование
}
)
)
};
Генерируется такой запрос
SELECT
[Project2].[ID] AS [ID],
[Project2].[C1] AS [C1],
[Project2].[DESCR] AS [DESCR],
[Project2].[SP101] AS [SP101],
[Project2].[C2] AS [C2],
[Project2].[SP80] AS [SP80],
[Project2].[DESCR1] AS [DESCR1]
FROM ( SELECT
[Limit1].[ID] AS [ID],
[Limit1].[DESCR] AS [DESCR],
[Limit1].[SP101] AS [SP101],
[Limit1].[C1] AS [C1],
[Join1].[SP80] AS [SP80],
[Join1].[DESCR] AS [DESCR1],
CASE WHEN ([Join1].[PARENTEXT] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM (SELECT TOP (1000)
[Extent1].[ID] AS [ID],
[Extent1].[DESCR] AS [DESCR],
[Extent1].[SP101] AS [SP101],
1 AS [C1]
FROM [dbo].[SC84] AS [Extent1] ) AS [Limit1]
LEFT OUTER JOIN (SELECT [Extent2].[PARENTEXT] AS [PARENTEXT], [Extent2].[SP80] AS [SP80], [Extent3].[DESCR] AS [DESCR]
FROM [dbo].[SC75] AS [Extent2]
INNER JOIN [dbo].[SC41] AS [Extent3] ON [Extent2].[SP79] = [Extent3].[ID] ) AS [Join1] ON ([Limit1].[ID] = [Join1].[PARENTEXT]) AND([Join1].[SP80] > N'4')
) AS [Project2]
ORDER BY [Project2].[ID] ASC, [Project2].[C2] ASC
И обращение к результатам уже как к дереву
foreach (var элем in qr.Take(1000))
{
foreach (var единица in элем.Единицы)
Console.WriteLine("{0}.{1} - {2}", элем.Наименование.TrimEnd(), единица.ШтрихКод, элем.ПолнНаименование.TrimEnd(), единица.ОКЕИ);
}
Так же в Linq можно склеивать запросы, накладывая новые ограничения. Это связано с ленивостью запросов
// Этот запрос получает значения периодического реквизита с ID 9697 в порядке убывания даты, времени, и строки документа
var query = from Константа in бд.ТаблицаКонстанты
where Константа.ID == 9697
orderby Константа.DATE descending, Константа.TIME descending, Константа.DOCID descending, Константа.ROW_ID descending
select Константа;
// Используем query в другом запросе добавив к на нему условия на элемент владелец и дату равную или меньше значение реквизита элемента справочника. В итоге получаем последнее значение и дату периодического элемента на дату заданную в реквизите элемента справочника.
var query2 = (from спр in бд.Спр_ДляПериодических
select new
{
Наименование = спр.Наименование,
ДатаСпр = спр.ДатаСпр,
Периодические = (from прериод in query.Where(х => х.OBJID == спр.ID && х.DATE <= спр.ДатаСпр).Take(1)
select new
{
Значение = прериод.VALUE,
Дата = прериод.DATE
}).FirstOrDefault()
}
);
Такая конструкция генерирует следующий запрос
SELECT
1 AS [C1],
[Extent1].[DESCR] AS [DESCR],
[Extent1].[SP9700] AS [SP9700],
[Limit1].[ROW_ID] AS [ROW_ID],
[Limit1].[VALUE] AS [VALUE],
[Limit1].[DATE] AS [DATE]
FROM [dbo].[SC9691] AS [Extent1]
OUTER APPLY (SELECT TOP (1) [Project1].[ROW_ID] AS [ROW_ID], [Project1].[DATE] AS [DATE], [Project1].[VALUE] AS [VALUE]
FROM ( SELECT
[Extent2].[ROW_ID] AS [ROW_ID],
[Extent2].[DATE] AS [DATE],
[Extent2].[VALUE] AS [VALUE],
[Extent2].[DOCID] AS [DOCID],
[Extent2].[TIME] AS [TIME]
FROM [dbo].[_1SCONST] AS [Extent2]
WHERE (9697 = [Extent2].[ID]) AND([Extent2].[OBJID] = [Extent1].[ID]) AND([Extent2].[DATE] <= [Extent1].[SP9700])
) AS [Project1]
ORDER BY [Project1].[DATE] DESC, [Project1].[TIME] DESC, [Project1].[DOCID] DESC, [Project1].[ROW_ID] DESC ) AS [Limit1]
Хочу обратить внимание на OUTER APPLY , которая будет прекрасно работать по индексу
Есть еще одна замечательная особенность - это использование переменных в запросе
Суть запроса такова. Сгруппируем единицы по владельцу, количество подчиненных которых превышает один элемент. Запрос будет выводить дерево по Владельцу, при этом в корне мы получаем агрегированные данные (Count())
var бд = Константы1С.ГлобальныйКонтекст.БД;
var qr = (from единица in бд.Спр_Единицы
group единица by new {
единица.ВладелецId,
Наименование=единица.Владелец.Наименование.TrimEnd()
}
into группа
let Количество = группа.Count()
where Количество > 1
select new
{
Наименование = группа.Key.Наименование,
Количество = Количество,
Группа = группа
});
foreach (var элем in qr)
{
// Console.WriteLine("{0}.{1} - ", элем.Наименование, элем.Количество);
foreach (var единица in элем.Группа)
{
Console.WriteLine("{0}.{1} - ", единица.ОКЕИ.Наименование, единица.ШтрихКод);
}
}
Генерирует такой запрос
SELECT
[Project1].[C2] AS [C1],
[Project1].[C3] AS [C2],
[Project1].[C1] AS [C3],
[Project1].[PARENTEXT] AS [PARENTEXT],
[Project1].[C4] AS [C4],
[Project1].[ID] AS [ID],
[Project1].[PARENTEXT1] AS [PARENTEXT1],
[Project1].[ISMARK] AS [ISMARK],
[Project1].[SP79] AS [SP79],
[Project1].[SP76] AS [SP76],
[Project1].[SP78] AS [SP78],
[Project1].[SP80] AS [SP80],
[Project1].[SP8752] AS [SP8752],
[Project1].[SP9519] AS [SP9519]
FROM ( SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[K1] AS [PARENTEXT],
[GroupBy1].[K2] AS [C2],
[GroupBy1].[K3] AS [C3],
[Join2].[ID1] AS [ID],
[Join2].[PARENTEXT] AS [PARENTEXT1],
[Join2].[ISMARK1] AS [ISMARK],
[Join2].[SP79] AS [SP79],
[Join2].[SP76] AS [SP76],
[Join2].[SP78] AS [SP78],
[Join2].[SP80] AS [SP80],
[Join2].[SP8752] AS [SP8752],
[Join2].[SP9519] AS [SP9519],
CASE WHEN ([Join2].[ID1] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C4]
FROM (SELECT
[Join1].[K1] AS [K1],
[Join1].[K2] AS [K2],
[Join1].[K3] AS [K3],
COUNT([Join1].[A1]) AS [A1]
FROM ( SELECT
[Extent1].[PARENTEXT] AS [K1],
1 AS [K2],
RTRIM([Extent2].[DESCR]) AS [K3],
1 AS [A1]
FROM [dbo].[SC75] AS [Extent1]
INNER JOIN [dbo].[SC84] AS [Extent2] ON [Extent1].[PARENTEXT] = [Extent2].[ID]
) AS [Join1]
GROUP BY [K1], [K2], [K3] ) AS [GroupBy1]
LEFT OUTER JOIN (SELECT [Extent3].[ID] AS [ID1], [Extent3].[PARENTEXT] AS [PARENTEXT], [Extent3].[ISMARK] AS [ISMARK1], [Extent3].[SP79] AS [SP79], [Extent3].[SP76] AS [SP76], [Extent3].[SP78] AS [SP78], [Extent3].[SP80] AS [SP80], [Extent3].[SP8752] AS [SP8752], [Extent3].[SP9519] AS [SP9519], [Extent4].[DESCR] AS [DESCR]
FROM [dbo].[SC75] AS [Extent3]
INNER JOIN [dbo].[SC84] AS [Extent4] ON [Extent3].[PARENTEXT] = [Extent4].[ID] ) AS [Join2] ON ([GroupBy1].[K1] = [Join2].[PARENTEXT]) AND(([GroupBy1].[K3] =(RTRIM([Join2].[DESCR]))) OR(([GroupBy1].[K3] IS NULL) AND(RTRIM([Join2].[DESCR]) IS NULL)))
WHERE [GroupBy1].[A1] > 1
) AS [Project1]
ORDER BY [Project1].[C2] ASC, [Project1].[PARENTEXT] ASC, [Project1].[C3] ASC, [Project1].[C4] ASC
Плоский вариант
var бд = Константы1С.ГлобальныйКонтекст.БД;
var qr = (from единица in бд.Спр_Единицы
group единица by new
{
единица.ВладелецId,
Наименование = единица.Владелец.Наименование.TrimEnd()
}
into группа
let Количество = группа.Count()
where Количество > 1
select new
{
Наименование = группа.Key.Наименование,
Количество = Количество,
});
foreach (var элем in qr)
{
Console.WriteLine("{0}.{1} - ", элем.Наименование, элем.Количество);
}
Генерирует следующий запрос
SELECT
1 AS [C1],
[GroupBy1].[K2] AS [C2],
[GroupBy1].[A1] AS [C3]
FROM ( SELECT
[Join1].[K1] AS [K1],
[Join1].[K2] AS [K2],
COUNT([Join1].[A1]) AS [A1]
FROM ( SELECT
[Extent1].[PARENTEXT] AS [K1],
RTRIM([Extent2].[DESCR]) AS [K2],
1 AS [A1]
FROM [dbo].[SC75] AS [Extent1]
INNER JOIN [dbo].[SC84] AS [Extent2] ON [Extent1].[PARENTEXT] = [Extent2].[ID]
) AS [Join1]
GROUP BY [K1], [K2]
) AS [GroupBy1]
WHERE [GroupBy1].[A1] > 1
В оптимальном варианте должен быть Having
Аналог Union All выглядит так
var query = (from x in db.Table1 select new { A = x.A, B = x.B })
.Concat(from y in db.Table2 select new { A = y.A, B = y.B });
Еще одна замечательная возможность можно использовать расширения.
Например, можно также и расширять условия
http://www.albahari.com/nutshell/predicatebuilder.aspx
https://github.com/scottksmith95/LINQKit
Можно, например, написать условие по OR
Можно написать обобщенную функцию
public IQueryable<TEntity> НайтиПоВхождениюВНаименование<TEntity>(paramsstring[] keywords) where TEntity : СправочникПредок
{
var predicate = PredicateBuilder.False<TEntity>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or(p => p.Наименование.Contains(temp));
}
return this.Set<TEntity>().AsExpandable().Where(predicate);
}
И использовать
var бд = Константы1С.ГлобальныйКонтекст.БД;
var запрос = бд.НайтиПоВхождениюВНаименование<Справочник.Номенклатура>("Linq", "Наше", "Все").Select(товар => товар.Наименование);
foreach (var товар in запрос)
{
Console.WriteLine("{0} ", товар);
}
Генерируется следующий запрос
SELECT
[Extent1].[DESCR] AS [DESCR]
FROM [dbo].[SC84] AS [Extent1]
WHERE ([Extent1].[DESCR] LIKE @p__linq__0 ESCAPE N'~')
OR([Extent1].[DESCR] LIKE @p__linq__1 ESCAPE N'~')
OR([Extent1].[DESCR] LIKE @p__linq__2 ESCAPE N'~')
Кроме того в Linq to EF можно использовать Entity SQL
Например
varstr = @"Select VALUE p From [Спр_Номенклатура] as p";
varЗапрос = qr.CreateQuery<Справочник.Номенклатура>(str );
var res = Запрос.Execute(MergeOption.NoTracking);
foreach (var элем in res)
{
Console.WriteLine("{0}.{1} - {2}", элем.Наименование.TrimEnd(), элем.Артикул, элем.ПолнНаименование.TrimEnd());
}
Но по большому счету выхлоп от него небольшой
https://msdn.microsoft.com/ru-ru/library/bb738573(v=vs.110).aspx
Отличия Entity SQL и Transact-SQL
Идентификаторы
В языке Transact-SQL сравнение идентификаторов всегда осуществляется с учетом параметров сортировки текущей базы данных. В Entity SQL идентификаторы всегда чувствительны к регистру и диакритическим знакам (то есть Entity SQL различает диакритические знаки, например, «а» отличается от «?»). Entity SQL обрабатывает версии букв, которые кажутся такими же, но являются другими символами и происходят из других кодовых страниц. Для получения дополнительной информации см. Набор символов ввода (Entity SQL).
Функциональность Transact-SQL, недоступная в Entity SQL
Следующая функциональность Transact-SQL недоступна в языке Entity SQL.
DML
В настоящее время язык Entity SQL не поддерживает инструкции DML (вставка, обновление, удаление).
DDL
Текущая версия Entity SQL не поддерживает DDL.
Командное программирование
Язык Entity SQL не поддерживает командное программирование в отличие от Transact-SQL. Используйте вместо этого языки программирования.
Функции группирования
Язык Entity SQL пока не поддерживает функции группирования (например, CUBE, ROLLUP и GROUPING_SET).
Функции аналитики
Язык Entity SQL не предоставляет (пока) поддержку функций аналитики.
Встроенные функции, операторы
Язык Entity SQL поддерживает подмножество встроенных функций и операторов Transact-SQL. Вероятно, эти операторы и функции будут реализованы ведущими поставщиками хранилищ. В языке Entity SQL используются специальные функции для хранилищ, объявленные в манифесте поставщика. Кроме того, модель Entity Framework позволяет объявлять встроенные и пользовательские функции хранилища для использования в Entity SQL.
Подсказки
Язык Entity SQL не предоставляет механизм подсказок в запросах.
Пакетирование результатов запроса
Entity SQL не поддерживает пакетирование результатов запросов. Например, допустим следующий запрос Transact-SQL (отправляемый как пакет):
select * from products;
select * from catagories;
Однако эквивалент Entity SQL не поддерживается.
Select value p from Products as p;
Select value c from Categories as c;
Entity SQL поддерживает только запросы, которые выдают один результат на одну команду.
