gifts2017

Linq to EF. Практика использования. Часть III

Опубликовал Сергей Смирнов (Serginio) в раздел Программирование - Практика программирования

Эта статья является продолжением Code First и Linq to EF на примере 1С версии 7.7 и 8.3. Здесь приведены примеры построения запросов и ссылки на интересные материалы

Данная статья является продолжением

http://infostart.ru/public/393228/

http://infostart.ru/public/402038/

 

Сразу приведу интересные ссылки

Интересные ссылки

http://www.dotnet-tricks.com/Tutorial/linq/UXPF181012-SQL-Joins-with-C

 

https://www.linqpad.net/WhyLINQBeatsSQL.aspx

 

http://www.infoworld.com/article/2934465/microsoft-net/best-practices-in-optimizing-linq-performance.html

 

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]

 

 

Хочу обратить внимание на    OUTERAPPLY , которая будет прекрасно работать по индексу

 

 

Есть еще одна замечательная особенность - это использование переменных в запросе

 

Суть запроса такова. Сгруппируем единицы по владельцу, количество подчиненных которых превышает один элемент. Запрос будет выводить дерево по Владельцу, при этом в корне мы получаем агрегированные данные (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 = @"SelectVALUE  pFrom [Спр_Номенклатура] asp";

           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 поддерживает только запросы, которые выдают один результат на одну команду.

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Евгений Стоянов (quick) 30.09.15 10:15
Отличная статья, много полезного нашел для себя.
Попутно пытаюсь сделать то же самое только на python, недавно выложил исходики https://github.com/WorldException/v7py
2. Сергей Смирнов (Serginio) 30.09.15 10:21
(1) Молодец. Перенес логику запросов 1С++. Сам хотел заняться аналогичным, но для 8 ки.
И спасибо за комментарий, а то меня все пинают, что никому это не нужно.
3. Игорь Богданов (avz_1C) 29.02.16 14:41
(2) Пинают ... Сначала, пинающие, пусть попытаются понять красоту вот этого фрагмента:

var qr = from Номенклатура in бд.Спр_Номенклатура
from единицы in бд.Спр_Единицы.Where(единица => единица.ВладелецId == Номенклатура.ID && единица.ШтрихКод.CompareTo("4")
>0).DefaultIfEmpty()
selectnew
{
Номенклатура.Наименование,
Номенклатура.ПолнНаименование,
единицы.ШтрихКод,
ОКЕИ = единицы.ОКЕИ.Наименование
};
Serginio; +1 Ответить
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа