Запрос соединения EF Core

В этом руководстве мы рассмотрим, как использовать запрос на присоединение в EF Core для загрузки данных из двух, трех и более таблиц. Оператор соединения LINQ позволяет нам объединять несколько таблиц в одном или нескольких столбцах (несколько столбцов). По умолчанию они выполняют внутреннее соединение таблиц. Мы также узнаем, как выполнять левые соединения в EF Core с помощью оператора соединения и метода DefaultIfEmpty. Также осталось соединение с предложением where.

Всегда рекомендуется использовать навигационные свойства для запроса связанных данных. Вы можете обратиться к статье «Запрос связанных данных с использованием навигационных свойств» из нашего последнего руководства. Вы должны использовать операторы запросов на присоединение только в том случае, если для таблиц не определены какие-либо навигационные свойства или если вы хотите точно настроить сгенерированные запросы для повышения производительности.

Использование основного соединения EF

Синтаксис запроса

Запросы соединений проще с синтаксисом запроса.

Следующий запрос объединяется Trackс MediaTypeтаблицей с помощью Joinоператора запроса. Оператор Joinиспользует Equalsключевое слово для сравнения двух или более столбцов. В этом примере мы используем столбец MediaTypeId. Запрос очень похож на обычные SQL запросы на присоединение к базе данных.

Запрос начинается с внешней таблицы, Trackт.е. Мы используем oпеременную диапазона, вы также можете использовать имя таблицы.

from o in db.Track

Используйте joinоператор для присоединения к внутренней таблице.

join i in db.MediaType

Упомяните условие, на котором вы хотите присоединиться к ним. Обратите внимание, что мы используем equals& not ==или =. Также мы можем сравнивать только на равенство. Другие сравнения пока не поддерживаются.

on o.MediaTypeId equals i.MediaTypeId

Наконец, чтобы выбрать столбцы, мы используем проекционные запросы. Вы можете выбрать свойства, используя переменную диапазона.

private void joinTwoTablesExample1()
{
    //Query Syntax
    using (ChinookContext db = new ChinookContext())
    {
        var Track = (from o in db.Track
                        join i in db.MediaType
                        on o.MediaTypeId equals i.MediaTypeId
                        select new
                        {
                            Name = o.Name,
                            Composer = o.Composer,
                            MediaType = i.Name
                        }).Take(5);
        foreach (var t in Track)
        {
            Console.WriteLine("{0} {1} {2}", t.Name, t.Composer, t.MediaType);
        }
    }
    Console.WriteLine("Press any key to continue");
    Console.ReadKey();
}

Приведенный выше запрос преобразуется в следующий SQL-запрос. Вы заметите, что он создает SQL INNER JOIN .

//SQL Query
SELECT TOP(@__p_0) [t].[Name], [t].[Composer], [m].[Name] AS [MediaType]
FROM [Track] AS [t]
INNER JOIN [MediaType] AS [m] ON [t].[MediaTypeId] = [m].[MediaTypeId]

Синтаксис метода

Синтаксис запроса метода использует joinметод. joinметод является методом расширения, который использует следующий синтаксис.

public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(
               this IEnumerable<TOuter> outer,
               IEnumerable<TInner> inner,
               Func<TOuter,?TKey> outerKeySelector,
               Func<TInner,?TKey> innerKeySelector,
               Func<TOuter,?TInner,?TResult> resultSelector
               )

IEnumerable<TOuter> outer

Первая последовательность для join. В нашем случае это Trackтаблица.

var Track = db.Track

IEnumerable<TInner> inner

Последовательность к joinпервой последовательности. Здесь мы используем MediaTypeстол, который мы собираемся к joinстолу Track.

Join(db.MediaType,

Func<TOuter, TKey> outerKeySelector

Функция для извлечения joinключа из первой последовательности ( Trackтаблица). Здесь мы используем лямбда-выражение l => l.MediaTypeId. Мы используем MediaTypeIdтаблицу Trackдля joinдвух таблиц

o => o.MediaTypeId,

Func<TInner, TKey> innerKeySelector

Функция для извлечения joinключа из второй последовательности ( MediaTypeтаблица). Это похоже на outerKeySelector, но указывает ключ, который будет использоваться из второй таблицы. В нашем случае это MediaTypeIdполе from MediaTypetable.

i => i.MediaTypeId,

Func<TOuter, TInner, TResult>

Функция для создания элемента результата из двух совпадающих элементов. Здесь два совпадающих элемента: TOuterнаша Trackтаблица ( o) и TInnerтаблица MediaType( i).

Мы используем проекцию на анонимный тип, чтобы вернуть результат.

(o, i) => new
    {
      Name = o.Name,
      Composer = o.Composer,
      MediaType = i.Name
    }

Окончательный запрос выглядит следующим образом

private void joinTwoTablesExample1()
{
    //Method Syntax
    using (ChinookContext db = new ChinookContext())
    {
        var Track = db.Track
            .Join(db.MediaType,
                o => o.MediaTypeId,
                i => i.MediaTypeId,
                (o, i) =>
                new
                {
                    Name = o.Name,
                    Composer = o.Composer,
                    MediaType = i.Name
                }
            ).Take(5);
        foreach (var t in Track)
        {
            Console.WriteLine("{0} {1} {2}", t.Name, t.Composer, t.MediaType);
        }
    }
    Console.WriteLine("Press any key to continue");
    Console.ReadKey();
}
//SQL Query
SELECT TOP(@__p_0) [t].[Name], [t].[Composer], [m].[Name] AS [MediaType]
FROM [Track] AS [t]
INNER JOIN [MediaType] AS [m] ON [t].[MediaTypeId] = [m].[MediaTypeId]

Вот еще один пример, где мы используем joinпредложение для соединения customerи employeeтаблицы. Как в методе, так и в синтаксисе запроса.

private void joinTwoTablesExample2()
{
    //Method Syntax
    using (ChinookContext db = new ChinookContext())
    {
        var Track = db.Customer
            .Join(db.Employee,
                f => f.SupportRepId,
                s => s.EmployeeId,
                (f, s) =>
                new
                {
                    CustomerName = f.FirstName,
                    CustomerState = f.State,
                    EmployeeName = s.FirstName,
                    EmployeeState = s.State,
                }
            ).Take(5);
        foreach (var t in Track)
        {
            Console.WriteLine("{0} {1} {2} {3}", t.CustomerName, t.CustomerState, t.EmployeeName, t.EmployeeState);
        }
    }
    Console.WriteLine("Press any key to continue");
    Console.ReadKey();
    //Query Syntax
    using (ChinookContext db = new ChinookContext())
    {
        var Track = (from f in db.Customer
                        join s in db.Employee
                        on f.SupportRepId equals s.EmployeeId
                        select new
                        {
                            CustomerName = f.FirstName,
                            CustomerState = f.State,
                            EmployeeName = s.FirstName,
                            EmployeeState = s.State,
                        }).Take(5);
        foreach (var t in Track)
        {
            Console.WriteLine("{0} {1} {2} {3}", t.CustomerName, t.CustomerState, t.EmployeeName, t.EmployeeState);
        }
    }
    Console.WriteLine("Press any key to continue");
    Console.ReadKey();
}      

SQL-запрос вышеуказанного метода

//SELECT TOP(@__p_0) [c].[FirstName] AS[CustomerName], [c].[State] AS[CustomerState], [e].[FirstName] AS[EmployeeName], [e].[State]
//AS[EmployeeState]
//FROM[Customer] AS[c]
//INNER JOIN[Employee] AS[e] ON[c].[SupportRepId] = [e].[EmployeeId]

Соединение LINQ в нескольких столбцах

Чтобы объединить две таблицы в более чем одном столбце (объединить с помощью составных ключей), нам нужно определить анонимный тип со значениями, которые мы хотим сравнить.

Синтаксис запроса

Например, в примере запроса inst

var result = (from m1 in db.model1
              join m2 in db.model2
               on        new { m1.field1 , m1.field2 }
                  equals new { m2.field1 , m2.field2 }
             select new
              {
                field1 = m1.field1,
                field2 = m1.field2,
                someField = m2.someField
             }).ToList();

В приведенном выше примере мы создаем анонимный тип для сравнения полей.

on new { m1.field1 , m1.field2 } equals new {m2.field1, m2.field2 }

Вышеприведенное работает только в том случае, если типы данных и имена свойств в анонимных типах совпадают.

Если имена свойств не совпадают, то вы должны назвать свойства анонимного типа, как показано ниже.

on new { p1=m1.field1 , p2=m1.field2 } equals new {p1=m2.fld1, p2=m2.fld2 }

Пример (синтаксис запроса)

var Track = db.Customer
    .Join(db.Employee,
        f => new { f1 = f.SupportRepId.Value, f2 = f.State },
        s => new { f1 = s.EmployeeId, f2 = s.State },
        (f, s) =>
        new
        {
            CustomerName = f.FirstName,
            CustomerState = f.State,
            EmployeeName = s.FirstName,
            EmployeeState = s.State,
        }
    ).Take(5);

Пример синтаксиса метода

var Track = (from f in db.Customer
                join s in db.Employee
                on new { f1 = f.SupportRepId.Value, f2 = f.State } equals new { f1 = s.EmployeeId, f2 = s.State }
                select new
                {
                    CustomerName = f.FirstName,
                    CustomerState = f.State,
                    EmployeeName = s.FirstName,
                    EmployeeState = s.State,
                }).Take(5);

Обратите внимание, что мы используем f.SupportRepId.Valueвместо f.SupportRepId. т.е. потому что тип данных SupportRepId( int?Nullable int). В то время как EmployeeIdэто int. Соединение завершается ошибкой, если тип данных не совпадает. Следовательно, мы используем f.SupportRepId.Valueдля преобразования int?вint

Объединение трех и более таблиц

Следующие запросы демонстрируют использование запросов соединения между тремя или более таблицами. Запрос ниже запрашивает все счета-фактуры для отслеживания Bohemian Rhapsodyс их qty& amount. Этот запрос включает в себя объединение трех Trackтаблиц InvoiceLine&Invoice

Синтаксис запроса

private void joinThreeTablesQuerySyntax()
{
    using (ChinookContext db = new ChinookContext())
    {
        var Track = (from t in db.Track
                        join il in db.InvoiceLine
                        on t.TrackId equals il.TrackId
                        join i in db.Invoice
                        on il.InvoiceId equals i.InvoiceId
                        where t.Name == "Bohemian Rhapsody"
                        select (new
                        {
                            TrackName = t.Name,
                            TrackId = t.TrackId,
                            InvoiceId = i.InvoiceId,
                            InvoiceDate = i.InvoiceDate,
                            Quantity = il.Quantity,
                            UnitPrice = il.UnitPrice
                        })
                    ).ToList();
        foreach (var r in Track)
        {
            Console.WriteLine("{0} {1} {2} {3}", r.TrackName, r.InvoiceDate, r.Quantity, r.UnitPrice);
        }
    }
}

SELECT[t].[Name] AS[TrackName], [t].[TrackId], [i0].[InvoiceId], [i0].[InvoiceDate], [i].[Quantity], [i].[UnitPrice]
FROM[Track] AS[t]
INNER JOIN[InvoiceLine] AS[i] ON[t].[TrackId] = [i].[TrackId]
INNER JOIN[Invoice] AS[i0] ON[i].[InvoiceId] = [i0].[InvoiceId]
WHERE[t].[Name] = N'Bohemian Rhapsody'

Синтаксис метода

Синтаксис метода достигает этого путем связывания метода соединения.

Первое соединение объединяет внешнюю таблицу с Tackвнутренней таблицей и использует проекцию для создания анонимного объекта.InvoiceLineTrackId

Для второго соединения предыдущий результат (анонимный объект) действует как внешняя таблица. Внутренняя таблица есть Invoice. Снова используйте проекцию, чтобы создать еще один анонимный объект.

Вы можете продолжать повторять соединение снова для большего количества таблиц. Наконец, используйте ToListметод выполнения запроса.

private void joinThreeTablesMethodSyntax()
{
    //Method Syntax
    using (ChinookContext db = new ChinookContext())
    {
        var Track = db.Track
           .Join(db.InvoiceLine,
                f => f.TrackId, s => s.TrackId,
                (f, s) => new { TrackName = f.Name, TrackId = f.TrackId, InvoiceId = s.InvoiceId, Quantity = s.Quantity, UnitPrice = s.UnitPrice }
                 )
                .Join(db.Invoice,
                     f => f.InvoiceId, s => s.InvoiceId,
                     (f, s) => new { TrackName = f.TrackName, TrackId = f.TrackId, InvoiceId = f.InvoiceId, InvoiceDate = s.InvoiceDate, Quantity = f.Quantity, UnitPrice = f.UnitPrice }
                     ).Where(r => r.TrackName == "Bohemian Rhapsody")
                    .ToList();
        foreach (var r in Track)
        {
            Console.WriteLine("{0} {1} {2} {3}", r.TrackName, r.InvoiceDate, r.Quantity, r.UnitPrice);
        }
    }
}

SQL-запрос выглядит следующим образом

SELECT[t].[Name] AS[TrackName], [t].[TrackId], [i].[InvoiceId], [i0].[InvoiceDate], [i].[Quantity], [i].[UnitPrice]
FROM[Track] AS[t]
INNER JOIN[InvoiceLine] AS[i] ON[t].[TrackId] = [i].[TrackId]
INNER JOIN[Invoice] AS[i0] ON[i].[InvoiceId] = [i0].[InvoiceId]
WHERE[t].[Name] = N'Bohemian Rhapsody'

Запрос также можно записать так, как показано ниже. Обратите внимание на разницу в проекции первого соединения и этого.

    var Track = db.Track
        .Join(db.InvoiceLine,
                f => f.TrackId, s => s.TrackId,
                (Track, InvoiceLine) => new { Track, InvoiceLine }       //Projecting entire row from both tables
            )
            .Join(db.Invoice,
                f => f.InvoiceLine.InvoiceId, s => s.InvoiceId,
                (f, s) => new { TrackName = f.Track.Name, TrackId = f.Track.TrackId, InvoiceId = f.InvoiceLine.InvoiceId, InvoiceDate = s.InvoiceDate, Quantity = f.InvoiceLine.Quantity, UnitPrice = f.InvoiceLine.UnitPrice }
                )
        .Where(r => r.TrackName == "Bohemian Rhapsody")
        .ToList();

Левое соединение

EF Core преобразует вышеуказанные соединения во ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Но другим наиболее часто используемым соединением является левое соединение SQL . Чтобы использовать левое соединение, мы используем метод DefaultIfEmptyв синтаксисе запроса.

Чтобы реализовать левое соединение, сначала мы начнем с обычного соединения двух таблиц. Используйте, into j1чтобы поместить результаты этого объединения во временную переменную.j1

       var model = (from t in db.Track
                     join il in db.InvoiceLine on t.TrackId equals il.TrackId into j1

Теперь предположим , что j1это другая таблица, и запустите другое соединение, как показано ниже. Здесь используйте метод DefaultIfEmpty, который указывает EF Core использовать левое соединение. Используйте, into j2чтобы поместить результаты этого объединения во временную переменную.j2

                     from j in j1.DefaultIfEmpty()
                     join i in db.Invoice on j.InvoiceId equals i.InvoiceId into j2

Вы можете продолжить это для большего количества объединений.

Наконец, в последнем fromвыражении используйте проекцию для выбора свойств вывода. Помните, что переменные диапазона из joinпредложения (например , il& i) будут недоступны. Доступна только переменная диапазона в fromпредложении, т.е. ( t, j, r)

public void joinLeftQuerySyntaxExample1()
{
    //Console.WriteLine("******************* Query Syntax ******************* ");
    using (ChinookContext db = new ChinookContext())
    {
        var model = (from t in db.Track
                     join il in db.InvoiceLine on t.TrackId equals il.TrackId into j1
                     from j in j1.DefaultIfEmpty()
                     join i in db.Invoice on j.InvoiceId equals i.InvoiceId into j2
                     from r in j2.DefaultIfEmpty()
                        select new
                        {
                            TrackName = t.Name,
                            TrackId = t.TrackId,
                            InvoiceId = r.InvoiceId,
                            InvoiceDate = r.InvoiceDate,
                            Quantity = j.Quantity,
                            UnitPrice = j.UnitPrice
                        })
                        .Where(r => r.TrackName == "Bohemian Rhapsody")
                        .ToList();
        foreach (var item in model)
        {
            Console.WriteLine("{0} {1} {2} {3}", item.TrackName, item.InvoiceDate, item.Quantity, item.UnitPrice);
        }
    }
    Console.WriteLine("Press any key to continue /Query Syntax 1");
    Console.ReadKey();
}

Приведенный выше код приводит к следующему оператору SQL.

SELECT [t].[Name] AS [TrackName], [t].[TrackId], [i0].[InvoiceId], [i0].[InvoiceDate], [i].[Quantity], [i].[UnitPrice]
FROM [Track] AS [t]
LEFT JOIN [InvoiceLine] AS [i] ON [t].[TrackId] = [i].[TrackId]
LEFT JOIN [Invoice] AS [i0] ON [i].[InvoiceId] = [i0].[InvoiceId]
WHERE [t].[Name] = N'Bohemian Rhapsody'

Обратите внимание, что если вы опустите DefaultIfEmpty, то SQL Server выполнит внутреннее соединение. Вы можете использовать его для создания запроса с левым и внутренним соединением.

Использование предложения «Где»

Вы также можете использовать следующий запрос, где мы использовали условие соединения в предложении where и used DefaultIfEmpty, но вы должны проверить окончательный запрос перед его использованием.

using (ChinookContext db = new ChinookContext())
{
    var model = (from t in db.Track
                 from il in db.InvoiceLine.Where(il => il.TrackId == t.TrackId).DefaultIfEmpty()
                 from i in db.Invoice.Where(i => i.InvoiceId == il.InvoiceId).DefaultIfEmpty()
                 select new
                  {
                      TrackName = t.Name,
                      TrackId = t.TrackId,
                      InvoiceId = i.InvoiceId,
                      InvoiceDate = i.InvoiceDate,
                      Quantity = il.Quantity,
                      UnitPrice = il.UnitPrice
                  })
                  .Where(r => r.TrackName == "Bohemian Rhapsody")
                    .ToList();
    foreach (var item in model)
    {
        Console.WriteLine("{0} {1} {2} {3}", item.TrackName, item.InvoiceDate, item.Quantity, item.UnitPrice);
    }
}