Entity framework объединение таблиц
Language Integrated Query (LINQ) contains many complex operators, which combine multiple data sources or does complex processing. Not all LINQ operators have suitable translations on the server side. Sometimes, a query in one form translates to the server but if written in a different form doesn't translate even if the result is the same. This page describes some of the complex operators and their supported variations. In future releases, we may recognize more patterns and add their corresponding translations. It's also important to keep in mind that translation support varies between providers. A particular query, which is translated in SqlServer, may not work for SQLite databases.
You can view this article's sample on GitHub.
The LINQ Join operator allows you to connect two data sources based on the key selector for each source, generating a tuple of values when the key matches. It naturally translates to INNER JOIN on relational databases. While the LINQ Join has outer and inner key selectors, the database requires a single join condition. So EF Core generates a join condition by comparing the outer key selector to the inner key selector for equality.
Further, if the key selectors are anonymous types, EF Core generates a join condition to compare equality component-wise.
Заключение
В этой статье я изложил свои размышления на тему JOIN локальной коллекции и DbSet. Мне показалось, что моя разработка с использованием VALUES может быть интересна сообществу. По крайней мере я не встречал такого подхода, когда решал эту задачу сам. Лично мне этот способ помог преодолеть ряд проблем с производительностью в моих текущих проектах, может быть он поможет и Вам.
Кто-то скажет, что использование MemoryJoin слишком "заумное" и его надо дорабатывать, а до тех пор использовать его не нужно. Это именно та причина, почему я очень сомневался и почти год не писал эту статью. Я соглашусь, что хотелось бы, чтобы это работало проще (надеюсь однажды так и будет), но также скажу, что оптимизация никогда не была задачей Junior’ов. Оптимизация всегда требует понимания как инструмент работает. И если есть возможность получить ускорение в ~8 раз (Naive Parallel vs MemoryJoin), то я бы осилил 2 пункта и документации.
И в заключении, диаграммы:
Затраченное время. Только 4 способа выполнили задачу за время менее 10 минут, а MemoryJoin — единственный способ, который выполнил задачу за время менее 10 секунд.
Потребление памяти. Все способы продемонстрировали примерно одинаковое потребление памяти, кроме Multiple Contains. Это связано с количеством возвращенных данных.
I'm trying to join three tables but I can't understand the method.
I completed join 2 tables
I would like to include tbl_Title table with TID PK and get Title field.
Please show a picture with expanded navigation properties. Navigation properties are ready-made joins.
Введение
Все знают про Entity Framework, многие используют его каждый день, и существует много хороших статей про то, как готовить его правильно (использовать более простые запросы, использовать параметры в Skip и Take, использовать VIEW, запрашивать только нужные поля, следить за кэшированием запросов и прочее), однако тема JOIN локальной коллекции и DbSet до сих пор является "слабым местом".
2 Answers 2
I think it will be easier using syntax-based query:
And you should probably add orderby clause, to make sure Top(10) returns correct top ten items.
Thank you very much for the method; works clear but I would like to see the answer as I asked, thanks a lot again.
I prefer the other syntax except when it comes to joins. the other syntax is so much more convoluted. I don't get it at all.
This is untested, but I believe the syntax should work for a lambda query. As you join more tables with this syntax you have to drill further down into the new objects to reach the values you want to manipulate.
@Dan Out of curiosity is it just the not thought out at all naming conventions with the c, cm, and ccm, or simply the syntax required to perform the joins using linq and lambda that is hideous? If the former, and you'd like to edit the post to have a better layout, by all means have at it. I'm still new to entity framework and am still soaking in best practices so if you have suggestions to make this answer more eloquent for future users I'd appreciate the assistance.
I hadn't given the exact reason much thought when I commented, but certainly the naming conventions hurt readability (obv. copied from from OP). Also, the commas as the beginning of the line hurt readability a lot (subjective, for me), and whitespace / indentation could be slightly improved. I've submitted an edit with all of these (IMHO) improvements since you requested it.
Code formatting is often biased, but there are general things which most people agree look better. As for naming conventions, I used to call things really short names, but I can type plenty fast enough now (not even considering things like Intellisense) that the few characters saved is not worth the detriment in readability versus naming things verbosely e.g. "EntryID" vs. "EID", "combinedEntry" vs. "cm", etc. Eventually, someone else is going to read my code, and I'd rather they not grow hatred for me as a linear function of the number of lines of my code they've had to read / maintain.
I just don't get the arguments against commas starting lines. I'm a firm believer, as it makes commenting out individual clauses/arguments really easy. And it looks prettier :-)
Readability and semantics aside, this is a better answer than the usual from x in context.table join . in my opinion. This way you can build your Joins and Where clauses dynamically and add paging and stuff later.
В Models\Course.cs замените ранее созданный код на:
Атрибут DatabaseGenerated
Атрибут DatabaseGenerated с параметром None, указанный для свойства CourseID, определяет то, что значение первичного ключа задаётся пользователем, а не генерируется базой данных.
По умолчанию Entity Framework предполагает автогенерацию первичных ключей базой данных, что и необходимо в большинстве ситуаций. Однако для сущности Course используются численные заданные пользователем значения, такие как 1000 для одного факультета, 2000 для другого и так далее.
Внешний ключ и Navigation Properties
Свойства-внешние ключи и navigation properties в сущности Course отражают следующие связи:
Курс ассоциирован с одним факультетом, таким образом, имеется внешний ключ DepartmentID и Department navigation property:
public int DepartmentID < get; set; >
public virtual Department Department
Курс может посещать неограниченное количество студентов, поэтому имеется Enrollments navigation property:
public virtual ICollection Enrollments
Курс может вестись различными преподавателями, поэтому имеется Instructors navigation property:
public virtual ICollection Instructors
Создание сущности Department
Создайте Models\Department.cs со следующим кодом:
Атрибут Column
Ранее атрибут Column мы использовали для изменения маппинга имени столбца. В коде для сущности Department этот атрибут используется для изменения маппинга типа данных SQL, то есть столбец будет определён в базе данных с типом данных SQL Server:
Обычно это не нужно, потому что Entity Framework автоматически подбирает наиболее подходящий тип данных исходя из типа CLR, который определён для свойства. Допустим, CLR тип decimal станет SQL Server типом decimal. Но в данном случае вы точно знаете, что свойство будет содержать цифры, связанные с валютой, и тип money будет наиболее подходящим для этого свойства.
Внешний ключ и Navigation Properties
Внешними ключами и navigation properties отражены следующие связи:
Факультет может как содержать, так и не содержать администратора, и администратор всегда = преподаватель. Поэтому свойство InstructorID определено как внешний ключ для сущности Instructor, и знак вопроса после типа int указывает на то, что свойство может быть nullable. Navigation property Administrator содержит сущность Instructor:
public int? InstructorID
public virtual Instructor Administrator < get; set; >
Факультет может иметь множество курсов, поэтому присутствует Courses navigation property:
public virtual ICollection Courses
Note Конвенциями определено, что Entity Framework каскадно удаляет non-nullable внешние ключи и в случаях связи многие-ко-многим. Это может привести к итеративному каскадному удалению, вызвав исключение при запуске кода. Допустим, если не определить Department.InstructorID как nullable, вы получите следующее исключение при: "The referential relationship will result in a cyclical reference that's not allowed."
Изменения, связанные с сущностью Student
В Models\Student.cs замените код на:
Изменения, касающиеся сущности Enrollment
В Models\Enrollment.cs замените код на:
Внешние ключи и Navigation Properties
Внешние ключи и navigation properties отражают следующие связи:
Каждой сущности записи на курс соответствует один курс, поэтому присутствует внешний ключ CourseID и Course navigation property:
public int CourseID
public virtual Course Course < get; set; >
Каждой сущности записи на курс соответствует один студент, поэтому присутствует внешний ключ StudentID и Student navigation property:
public int StudentID
public virtual Student Student
Связи многие-ко-многим
Сущности Student и Course связаны друг с другом связью многие-ко-многим, и сущность Enrollment соответствует and the Enrollment entity corresponds to a many-to-many join table with payload in the database. Это значит, что таблица Enrollment содержит дополнительные данные помимо внешних ключей для объединённых таблиц (в нашем случае первичный ключ и свойство Grade).
На изображении ниже представлены связи в виде диаграммы сущностей, сгенерированной Entity Framework designer.
Линия каждой связи имеет 1 на одном конце и * на другом, обозначая связь один-ко-многим.
Если таблица Enrollment не содержит информацию об оценках, необходимо иметь только два внешних ключа CourseID и StudentID. In that case, it would correspond to a many-to-many join table without payload (or a pure join table) in the database, and you wouldn't have to create a model class for it at all. Сущности Instructor и Course связаны подобной связью многие-ко-многим, и, как вы видите, между ними нет класса сущности:
Хотя объединённая таблица необходима:
Entity Framework автоматически создаёт таблицу CourseInstructor, доступ к которой осуществляется косвенно, а именно через Instructor.Courses и Course.Instructors navigation properties.
Атрибут DisplayFormat
Атрибут DisplayFormat для свойства Grade определяет форматирование для элемента:
- Оценка выводится в формате "3.5" или "4.0".
- Такое же форматирование оценка имеет и в режиме.
- Если оценки нет, выводится надпись "No grade".
Связи на диаграмме сущностей
Диаграмма ниже демонстрирует систему связей для модели School.
Кроме связей многие-ко-многим (*-*) и один-ко-многим (1-*), можно также увидеть связь один-к-нулю-или-одному (1-0..1) между сущностями Instructor и OfficeAssignment и нуль-к-одному-или-ко-многим (0..1 — *) Department и Instructor.
Настройка контекста базы данных
Дальше мы добавим новые сущности в класс SchoolContext и настроим мапинг. В некоторых случаях необходимо будет использовать методы вместо атрибутов из-за того, что для некоторой функциональности атрибутов просто не сущнствует. В других случаях можно выбирать, использовать методы или атрибуты (некоторые люди предпочитают не использовать атрибуты).
Замените код в DAL\SchoolContext.cs на:
В методе OnModelCreating определяются следующие связи:
Один-к-нулю-или-одному между Instructor и OfficeAssignment:
modelBuilder.Entity().HasOptional(p => p.OfficeAssignment).WithRequired(p => p.Instructor);
Многие-ко-многим между Instructor и Course. Код определяет таблицу и столбцы для объединённой таблицы. Code First может настроить связь многие-ко-многим и без кода, но если вы его не вызовете, то для столбцов будут взяты стандартные имена, такие как InstructorInstructorID для InstructorID.
modelBuilder.Entity()
.HasMany(c => c.Instructors).WithMany(i => i.Courses)
.Map(t => t.MapLeftKey("CourseID")
.MapRightKey("InstructorID")
.ToTable("CourseInstructor"));
Один-к-нулю-или-одному между Department и Instructor, с помощью Department.Administrator navigation property:
Заполнение базы данных тестовыми данными
Перед этим вы создавали DAL\SchoolInitializer.cs для заполнения базы тестовыми данными. Теперь замените старый код на новый, который учитывает присутствие новых сущностей.
Обратите внимание на обработку сущности Course, которая связана связью многие-ко-многим с сущностью Instructor:
При создании объекта Course как пустая коллекция (Instructors = new List()), что делает возможным добавление сущностей Instructor, связанных с Course, с помощью метода Instructor.Add(). Если вы не создали пустой List, у вас не получится добавлять подобные отношения, потому что свойство Instructors будет равно null и не будет иметь метода Add.
Note Не забывайте перед развертыванием проекта на production-сервер удалять весь код инициализации базы.
Удаление и пересоздание базы данных
Запустите проект и выберите страницу Student Index.
Если страница не открывается или вы получаете ошибку о том, что файл School.sdf уже используется (изображение ниже), необходимо еще раз открыть Server Explorer и закрыть подключение к базе и затем попробовать снова открыть страницу.
После этого откройте базу в Server Explorer и посмотрите в Tables новые таблицы.
Кроме EdmMetadata обратите внимание на таблицу, для которой вы не создавали класса CourseInstructor. Это объединённая из Instructor и Course таблица.
Щелкните на таблице CourseInstructor и нажмите Show Table Data чтобы убедиться в наличии данных, добавленных ранее Course.Instructors navigation property.
Теперь у вас есть сложная модель данных и соответствующая база данных. Дальше мы научимся разным способам обращения к данным.
Селектор коллекции ссылается на внешний источник не в предложении WHERE
Если селектор коллекции ссылается на внешний элемент, который не входит в предложение WHERE (как в случае выше), он не преобразуется в соединение базы данных. Поэтому необходимо оценить селектор коллекции для каждого внешнего элемента. Во многих реляционных базах данных такой оператор преобразуется в операции APPLY . Если коллекция для внешнего элемента пуста, то для него результаты отсутствуют. Но если к селектору коллекции применяется DefaultIfEmpty , внешний элемент соединяется со значением внутреннего элемента по умолчанию. В связи с этим различием запросы такого типа преобразуются в CROSS APPLY при отсутствии DefaultIfEmpty и в OUTER APPLY , если DefaultIfEmpty применяется. Некоторые базы данных, такие как SQLite, не поддерживают операторы APPLY , поэтому запросы такого типа могут не преобразовываться.
SelectMany
Оператор SelectMany в LINQ позволяет перечислять селектор коллекции для каждого внешнего элемента и создавать кортежи значений из каждого источника данных. Таким образом, это соединение, но без каких-либо условий, поэтому каждый внешний элемент соединяется с элементом из источника коллекции. В зависимости от того, как селектор коллекции связан с внешним источником данных, оператор SelectMany может преобразовываться в различные запросы на стороне сервера.
Left Join
While Left Join isn't a LINQ operator, relational databases have the concept of a Left Join which is frequently used in queries. A particular pattern in LINQ queries gives the same result as a LEFT JOIN on the server. EF Core identifies such patterns and generates the equivalent LEFT JOIN on the server side. The pattern involves creating a GroupJoin between both the data sources and then flattening out the grouping by using the SelectMany operator with DefaultIfEmpty on the grouping source to match null when the inner doesn't have a related element. The following example shows what that pattern looks like and what it generates.
The above pattern creates a complex structure in the expression tree. Because of that, EF Core requires you to flatten out the grouping results of the GroupJoin operator in a step immediately following the operator. Even if the GroupJoin-DefaultIfEmpty-SelectMany is used but in a different pattern, we may not identify it as a Left Join.
В языке LINQ есть множество сложных операторов, которые объединяют несколько источников данных или производят сложную обработку. Не для всех операторов LINQ есть подходящие преобразования на стороне сервера. Иногда запрос в одной форме преобразуется на сервере, но в другой — не преобразуется, даже если результат совпадает. На этой странице описываются некоторые сложные операторы и их поддерживаемые варианты. В будущих выпусках, возможно, будет поддерживаться больше шаблонов и будут добавлены соответствующие преобразования. Также важно иметь в виду, что поддержка преобразований зависит от поставщика. Запрос, который преобразуется в SqlServer, может не работать в базах данных SQLite.
Для этой статьи вы можете скачать пример из репозитория GitHub.
Оператор Join в LINQ позволяет соединять два источника данных на основе селектора ключа для каждого источника, создавая кортеж значений при совпадении ключей. В реляционных базах данных он естественным образом преобразуется в INNER JOIN . Если оператор Join в LINQ имеет внешний и внутренний селекторы ключей, база данных требует одного условия соединения. Таким образом, EF Core создает условие соединения, сравнивая внешний и внутренний селекторы ключей на равенство.
Кроме того, если селекторы ключей являются анонимными типами, EF Core создает условие соединения для покомпонентного сравнения на равенство.
Задача
Предположим, что есть база данных с ценами и есть коллекция транзакций у которой надо проверить корректность цен. И, предположим, у нас есть следующий код.
Этот код не работает в Entity Framework 6 вообще. В Entity Framework Core — работает, но всё будет выполнено на стороне клиента и в случае, когда в базе миллионы записей — это не выход.
Я также снимал некоторые метрики: затраченное время и потребление памяти. Оговорка: если тест выполнялся более 10 минут — я его прерывал (ограничение сверху). Машина для тестов Intel Core i5, 8 GB RAM, SSD.
Только 3 таблицы: prices, securities and price sources. Prices — содержит 10 миллионов записей.
Способ 1. Naive
Начнём с простого и будем использовать следующий код:
Идея проста: в цикле читаем записи из базы по одной и добавляем в результирующую коллекцию. У этого кода только одно преимущество — простота. И один недостаток — низкая скорость: даже при условии наличия индекса в базе, большая часть времени займёт коммуникация с сервером БД. Метрики получились такие:
Потребление памяти невелико. Для большой коллекции требуется 1 минута. Для начала неплохо, но хочется быстрее.
Способ 2. Naive parallel
Попробуем добавить параллелизм. Идея в том, чтобы обращаться к базе из нескольких потоков.
Для маленьких коллекций этот подход работает даже медленнее, чем первый способ. А для самого большого — в 2 раза быстрее. Интересно, что на моей машине было порождено 4 потока, но это не привело к 4х кратному ускорению. Это говорит о том, что накладные расходы в этом способе существенны: как на стороне клиента, так и на стороне сервера. Потребление памяти выросло, но незначительно.
Способ 3. Multiple Contains
Время попробовать нечто иное и попытаться свести задачу к выполнению одного запроса. Можно сделать следующим образом:
- Подготовить 3 коллекции уникальных значений Ticker, PriceSourceId и Date
- Выполнить запрос и использовать 3 Contains
- Перепроверить результаты локально
Проблема здесь в том, что время выполнения и объем возвращаемых данных сильно зависит от самих данных (и в запросе и в базе). То есть может вернуться набор только необходимых данных, а могут вернуться ещё и лишние записи (даже в 100 раз больше).
Это можно объяснить, используя следующий пример. Предположим есть следующая таблица с данными:
Однако в результате будет возвращено 4 записи, потому что они действительно соответствуют этим комбинациям. Плохо это тем, что чем больше полей используется — тем больше шанс получить лишние записи в результате.
По этой причине данные, полученные этим способом необходимо дополнительно фильтровать на стороне клиента. И это же является самым большим недостатком.
Метрики получились следующими:
Потребление памяти — хуже всех предыдущих способов. Количество прочитанных строк многократно превышает количество запрошенных. Тесты для больших коллекций были прерваны так как выполнялись больше 10 минут. Этот способ не годится.
Способ 4. Predicate builder
Попробуем теперь с другой стороны: старые добрые Expression. Используя их, можно построить 1 большой запрос в следующей форме:
… (.. AND .. AND ..) OR (.. AND .. AND ..) OR (.. AND .. AND ..) …
Это даёт надежду на то, что удастся построить 1 запрос и получить только нужные данные за 1 заход. Код:
Код получился более сложный, чем в предыдущих способах. Строить Expression вручную не самая простая и не самая быстрая операция.
Временные результаты получились ещё хуже, чем в предыдущем способе. Похоже, что накладные расходы при построении и при проходе по дереву оказались намного больше, чем выигрыш от использования одного запроса.
Способ 5. Shared query data table
Попробуем теперь другой вариант:
Я создал в базе новую таблицу, в которую буду записывать данные, необходимые для выполнения запроса (подспудно нужен новый DbSet в контексте).
Теперь, чтобы получить результат нужно:
- Начать транзакцию
- Загрузить данные запроса в новую таблицу
- Выполнить сам запрос (используя новую таблицу)
- Откатить транзакцию (чтобы очистить таблицу данных для запросов)
Код выглядит так:
Все тесты отработали и отработали быстро! Потребление памяти тоже приемлемое.
Таким образом, благодаря использованию транзакции эта таблица может использоваться одновременно несколькими процессами. И так как это реально существующая таблица, нам доступны все возможности Entity Framework: необходимо только загрузить данные в таблицу, построить запрос с использованием JOIN и выполнить. На первый взгляд — это то, что нужно, но есть и существенные минусы:
- Необходимо создать таблицу для конкретного типа запросов
- Необходимо использовать транзакции (и тратить ресурсы СУБД на них)
- Да и сама идея, что нужно что-то ПИСАТЬ, когда нужно ЧИТАТЬ, выглядит странно. А на Read Replica это просто не будет работать.
А в остальном — решение более или менее рабочее, которое уже можно использовать.
Способ 6. MemoryJoin extension
Теперь можно попробовать улучшить предыдущий подход. Размышления такие:
- Вместо использования таблицы, которая специфичная для одного типа запроса, можно использовать некий обобщенный вариант. А именно создать таблицу с именем вроде shared_query_data, и добавить в неё по несколько полей Guid, несколько Long, несколько String и т.д. Имена можно взять простые: Guid1, Guid2, String1, Long1, Date2, и т.д. Тогда эту таблицу можно будет использовать для 95% типов запросов. Имена свойств можно будет "скорректировать" позже при помощи проекции Select.
- Далее нужно добавить DbSet для shared_query_data.
- А что если вместо записи данных в базу — передавать значения, используя конструкцию VALUES? То есть необходимо, чтобы в итоговом SQL запросе вместо обращения к shared_query_data было обращение к VALUES. Как это сделать?
- В Entity Framework Core — просто используя FromSql.
- В Entity Framework 6 — придётся использовать DbInterception — то есть менять сгенерированный SQL, добавляя конструкцию VALUES прямо перед выполнением. Это приведет к ограничению: в одном запросе — не более одной конструкции VALUES. Но работать будет!
- На вход поступила коллекция объектов следующего типа:
- У нас в распоряжении есть DbSet с полями String1, String2, Date1, Long1, etc
- Пусть Ticker будет храниться в String1, TradedOn в Date1, а PriceSourceId в Long1 (int маппится в long, чтобы не делать отдельно поля для int и long)
- Тогда FromSql + VALUES будет таким:
- Теперь можно сделать проекцию и вернуть удобный IQueryable, использующий тот же тип, который был на входе:
Мне удалось реализовать этот подход и даже оформить его как NuGet пакет EntityFrameworkCore.MemoryJoin (код тоже доступен). Несмотря на то, что в имени есть слово Core, Entity Framework 6 тоже поддерживается. Я назвал его MemoryJoin, но по факту он отправляет локальные данные на СУБД в конструкции VALUES и вся работа выполняется на нём.
Код получается следующим:
Это лучший результат из всех, которые я пробовал. Код получился очень простым и понятным, и в то же время рабочим для Read Replica.
Здесь также видно, как обобщенная модель (с полями String1, Date1, Long1) при помощи Select превращается в ту, которая используется в коде (с полями Ticker, TradedOn, PriceSourceId).
Вся работа выполняется за 1 запрос на SQL сервере. И это и есть небольшой хэппи-энд, о котором я говорил в начале. И всё же использование этого способа требует понимания и следующих шагов:
- Необходимо добавить дополнительный DbSet в свой контекст (хотя саму таблицу можно не добавлять)
- В обобщенной модели, которая используется по умолчанию, объявлены по 3 поля типов Guid, String, Double, Long, Date и т.д. Этого должно хватить на 95% типов запросов. И если передать в FromLocalList коллекцию объектов с 20 полями, то будет выброшен Exception, говорящий, что объект слишком сложный. Это мягкое ограничение и его можно обойти — можно объявить свой тип и внести туда хоть по 100 полей. Однако, больше полей — медленней работа.
- Больше технических деталей описано в моей статье.
Collection selector references outer in a non-where case
When the collection selector references the outer element, which isn't in a where clause (as the case above), it doesn't translate to a database join. That's why we need to evaluate the collection selector for each outer element. It translates to APPLY operations in many relational databases. If the collection is empty for an outer element, then no results would be generated for that outer element. But if DefaultIfEmpty is applied on the collection selector then the outer element will be connected with a default value of the inner element. Because of this distinction, this kind of queries translates to CROSS APPLY in the absence of DefaultIfEmpty and OUTER APPLY when DefaultIfEmpty is applied. Certain databases like SQLite don't support APPLY operators so this kind of query may not be translated.
SelectMany
The LINQ SelectMany operator allows you to enumerate over a collection selector for each outer element and generate tuples of values from each data source. In a way, it's a join but without any condition so every outer element is connected with an element from the collection source. Depending on how the collection selector is related to the outer data source, SelectMany can translate into various different queries on the server side.
Левое соединение
Хотя в LINQ нет оператора левого соединения, в запросах к реляционным базам данных левое соединение используется часто. Определенный шаблон в запросах LINQ дает тот же результат, что и оператор LEFT JOIN на сервере. EF Core распознает такой шаблон и создает эквивалентный оператор LEFT JOIN на стороне сервера. Шаблон предполагает создание соединения GroupJoin между двумя источниками данных и последующее преобразование группирования в плоскую структуру с помощью оператора SelectMany с DefaultIfEmpty, применяемого к источнику группирования, для сопоставления со значением NULL, когда во внутреннем источнике нет соответствующего элемента. В приведенном ниже примере показано, как выглядит этот шаблон и какой результат он дает.
Приведенный выше шаблон создает сложную структуру в дереве выражения. По этой причине EF Core требует преобразовать результаты группирования с помощью оператора GroupJoin в плоскую структуру сразу после этого оператора. Если конструкция GroupJoin-DefaultIfEmpty-SelectMany используется в рамках другого шаблона, она может не распознаваться как левое соединение.
Чуть больше года при моём участии состоялся следующий "диалог":
Entity Framework:
Классика! Думаю многим знакома эта ситуация: когда очень хочется “красиво” и быстро сделать поиск в базе, используя JOIN локальной коллекции и DbSet. Обычно этот опыт разочаровывает.
В данной статье (которая является вольным переводом другой моей статьи) я проведу ряд экспериментов и попробую разные способы, чтобы обойти это ограничение. Будет код (несложный), размышления и что-то вроде хэппи-энда.
Collection selector references outer in a where clause
When the collection selector has a where clause, which references the outer element, then EF Core translates it to a database join and uses the predicate as the join condition. Normally this case arises when using collection navigation on the outer element as the collection selector. If the collection is empty for an outer element, then no results would be generated for that outer element. But if DefaultIfEmpty is applied on the collection selector then the outer element will be connected with a default value of the inner element. Because of this distinction, this kind of queries translates to INNER JOIN in the absence of DefaultIfEmpty and LEFT JOIN when DefaultIfEmpty is applied.
Селектор коллекции ссылается на внешний источник в предложении WHERE
Если в селекторе коллекции есть предложение WHERE, которое ссылается на внешний элемент, то EF Core преобразует его в соединение базы данных и использует предикат в качестве условия соединения. Обычно такое бывает при использовании свойства навигации по коллекции для внешнего элемента в качестве селектора коллекции. Если коллекция для внешнего элемента пуста, то для него результаты отсутствуют. Но если к селектору коллекции применяется DefaultIfEmpty , внешний элемент соединяется со значением внутреннего элемента по умолчанию. В связи с этим различием запросы такого типа преобразуются в INNER JOIN при отсутствии DefaultIfEmpty и в LEFT JOIN , если DefaultIfEmpty применяется.
GroupJoin
Оператор GroupJoin в LINQ позволяет соединять два источника данных, так же как оператор JOIN, но создает группу внутренних значений для соответствующих внешних элементов. Выполнение приведенного ниже запроса дает результат Blog & IEnumerable . Так как базы данных (особенно реляционные) обычно не позволяют представлять коллекцию объектов на стороне клиента, GroupJoin во многих случаях не преобразуется на сервере. Для выполнения GroupJoin без специального селектора требуется получить все данные с сервера (первый запрос ниже). Но если селектор ограничивает выбор данных, то получение всех данных с сервера может вызвать проблемы с производительностью (второй запрос ниже). Вот почему EF Core не преобразует оператор GroupJoin.
Селектор коллекции не ссылается на внешний источник
Если селектор коллекции не ссылается на элементы во внешнем источнике, результатом является декартово произведение обоих источников данных. В реляционных базах данных такой оператор преобразуется в CROSS JOIN .
GroupBy
LINQ GroupBy operators create a result of type IGrouping where TKey and TElement could be any arbitrary type. Furthermore, IGrouping implements IEnumerable , which means you can compose over it using any LINQ operator after the grouping. Since no database structure can represent an IGrouping , GroupBy operators have no translation in most cases. When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases. The SQL GROUP BY is restrictive too. It requires you to group only by scalar values. The projection can only contain grouping key columns or any aggregate applied over a column. EF Core identifies this pattern and translates it to the server, as in the following example:
EF Core also translates queries where an aggregate operator on the grouping appears in a Where or OrderBy (or other ordering) LINQ operator. It uses HAVING clause in SQL for the where clause. The part of the query before applying the GroupBy operator can be any complex query as long as it can be translated to server. Furthermore, once you apply aggregate operators on a grouping query to remove groupings from the resulting source, you can compose on top of it like any other query.
The aggregate operators EF Core supports are as follows
GroupBy
Операторы GroupBy в LINQ создают результат типа IGrouping , где TKey и TElement могут быть произвольного типа. Кроме того, IGrouping реализует интерфейс IEnumerable . Это означает, что после группирования можно производить композицию с помощью любого оператора LINQ. Так как ни одна структура базы данных не может представлять IGrouping , в большинстве случаев операторы GroupBy не преобразуются. Если к каждой группе применяется статистический оператор, возвращающий скалярное значение, в реляционных базах данных его можно преобразовать в оператор SQL GROUP BY . Применение оператора SQL GROUP BY также ограничено. Он требует выполнять группирование только по скалярным значениям. Проекция может содержать только ключевые столбцы группирования или любое статистическое выражение, примененное к столбцу. EF Core распознает этот шаблон и преобразует его на стороне сервера, как показано в следующем примере:
EF Core также преобразует запросы, в которых статистический оператор, выполняющий действия над группированием, включен в оператор LINQ Where или OrderBy (либо другое упорядочение). Для предложения WHERE в SQL используется предложение HAVING . Часть запроса перед применением оператора GroupBy может быть любым сложным запросом, который может быть преобразован на стороне сервера. Кроме того, после применения статистических операторов к запросу группирования для удаления группирований из результирующего источника этот запрос можно использовать для композиции, как и любой другой запрос.
EF Core поддерживает следующие статистические операторы:
GroupJoin
The LINQ GroupJoin operator allows you to connect two data sources similar to Join, but it creates a group of inner values for matching outer elements. Executing a query like the following example generates a result of Blog & IEnumerable . Since databases (especially relational databases) don't have a way to represent a collection of client-side objects, GroupJoin doesn't translate to the server in many cases. It requires you to get all of the data from the server to do GroupJoin without a special selector (first query below). But if the selector is limiting data being selected then fetching all of the data from the server may cause performance issues (second query below). That's why EF Core doesn't translate GroupJoin.
Collection selector doesn't reference outer
When the collection selector isn't referencing anything from the outer source, the result is a cartesian product of both data sources. It translates to CROSS JOIN in relational databases.
Читайте также: