Синтаксис метода LINQ с INNER и OUTER Join

Question

У меня есть 3 класса и пытаюсь использовать LINQ methods выполнить INNER JOIN и LEFT JOIN, Я могу исполнять каждый по отдельности, но мне не повезло вместе, так как я даже не могу понять синтаксис.

В конечном счете, SQL-код, который я бы написал, был бы:

SELECT *
FROM [Group] AS [g]
INNER JOIN [Section] AS [s] ON [s].[GroupId] = [g].[Id]
LEFT OUTER JOIN [Course] AS [c] ON [c].[SectionId] = [s].[Id]

Классы

public class Group {
    public int Id { get; set; }
    public int Name { get; set; }
    public bool IsActive { get; set; }
    public ICollection<Section> Sections { get; set; }
}

public class Section {
    public int Id { get; set; }
    public int Name { get; set; }
    public int GroupId { get; set; }
    public Group Group { get; set; }
    public bool IsActive { get; set; }
    public ICollection<Course> Courses { get; set; }
}

public class Course {
    public int Id { get; set; }
    public int UserId { get; set; }
    public int Name { get; set; }
    public int SectionId { get; set; }
    public bool IsActive { get; set; }
}

образцы

Я хочу, чтобы результат был типа Group, Я успешно выполнил LEFT JOIN между Section а также Course, но тогда у меня есть объект типа IQueryable<а>, which is not what I want, sinceGroup`.

var result = db.Section
               .GroupJoin(db.Course, 
                    s => s.Id,
                    c => c.SectionId,
                    (s, c) => new { s, c = c.DefaultIfEmpty() })
               .SelectMany(s => s.c.Select(c => new { s = s.s, c }));

Я тоже пробовал это, но возвращает NULL потому что это выполняет INNER JOIN на всех таблицах, а пользователь не вводил ни одного Courses,

var result = db.Groups
               .Where(g => g.IsActive)
               .Include(g => g.Sections)
               .Include(g => g.Sections.Select(s => s.Courses))
               .Where(g => g.Sections.Any(s => s.IsActive && s.Courses.Any(c => c.UserId == _userId && c.IsActive)))
               .ToList();

Вопрос

Как я могу выполнить INNER и LEFT JOIN с наименьшим количеством вызовов в базу данных и получить результат типа Group?

Желаемый результат

Я хотел бы иметь 1 объект типа Group, но только до тех пор, пока Group имеет Section, Я также хочу вернуть Courses пользователь имеет для конкретного Section или вернуться NULL,

Answer 1

Я думаю, то, что вы просите, невозможно без возврата нового (анонимного) объекта вместо Group (как показано в этом ответе). EF не позволит вам получить отфильтрованный Course коллекция внутри Section из-за того, как работают отношения и кэширование сущностей, это означает, что вы не можете использовать навигационные свойства для этой задачи.

Прежде всего, вы хотите контролировать загрузку связанных сущностей, поэтому я предлагаю включить отложенную загрузку, отметив Sections а также Courses свойства коллекции как virtual в ваших сущностях (если вы не включили отложенную загрузку для всех сущностей в вашем приложении), так как мы не хотим, чтобы EF загружал связанные Sections а также Courses так как это все равно загрузит все курсы для каждого пользователя.

public class Group {
    public int Id { get; set; }
    public int Name { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<Section> Sections { get; set; }
}

public class Section {
    public int Id { get; set; }
    public int Name { get; set; }
    public int GroupId { get; set; }
    public Group Group { get; set; }
    public bool IsActive { get; set; }
    public virtual ICollection<Course> Courses { get; set; }
}

В синтаксисе метода запрос, вероятно, будет выглядеть примерно так:

var results = db.Group
    .Where(g => g.IsActive)
    .GroupJoin(
        db.Section.Where(s => s.IsActive),
        g => g.Id,
        s => s.GroupId,
        (g, s) => new
        {
            Group = g,
            UserSections = s
                .GroupJoin(
                    db.Course.Where(c => c.IsActive && c.UserId == _userId).DefaultIfEmpty(),
                    ss => ss.Id,
                    cc => cc.SectionId,
                    (ss, cc) => new
                    {
                        Section = ss,
                        UserCourses = cc
                    }
                )
        })
    .ToList();

И вы будете использовать результат как:

foreach (var result in results)
{
    var group = result.Group;

    foreach (var userSection in result.UserSections)
    {
        var section = userSection.Section;

        var userCourses = userSection.UserCourses;

    }
}

Теперь, если вам не нужна дополнительная фильтрация результатов группы на уровне базы данных, вы также можете перейти к подходам INNER JOIN и LEFT OUTER JOIN, используя этот запрос LINQ, и выполнить группировку в памяти:

var results = db.Group
    .Where(g => g.IsActive)
    .Join(
        db.Section.Where(s => s.IsActive),
        g => g.Id,
        s => s.GroupId,
        (g, s) => new
        {
            Group = g,
            UserSection = new
            {
                Section = s,
                UserCourses = db.Course.Where(c => c.IsActive && c.UserId == _userId && c.SectionId == s.Id).DefaultIfEmpty()
            }
        })
    .ToList() // Data gets fetched from database at this point
    .GroupBy(x => x.Group) // In-memory grouping
    .Select(x => new
    {
        Group = x.Key,
        UserSections = x.Select(us => new
        {
            Section = us.UserSection,
            UserCourses = us.UserSection.UserCourses
        })
    });

Помните, всякий раз, когда вы пытаетесь получить доступ group.Sections или же section.Courses, вы запустите ленивую загрузку, которая выберет все дочерние секции или курсы, независимо от _userId,

Answer 2

Использование DefaultIfEmpty выполнить внешнее левое соединение

from g in db.group
join s in db.section on g.Id equals s.GroupId 
join c in db.course on c.SectionId equals s.Id into courseGroup
from cg in courseGroup.DefaultIfEmpty()
select new { g, s, c }; 

Answer 3

Тип вашего SQL не [Группа] (Тип группы будет: выберите [Группа].* Из...), в любом случае, если вы хотите, чтобы это так, то в простой форме это будет:

var result = db.Groups.Where( g => g.Sections.Any() );

Однако, если вы действительно хотите конвертировать ваш SQL, то:

var result = from g in db.Groups
             from s in g.Sections
             from c in s.Courses.DefaultIfEmpty()
             select new {...};

Даже это будет делать:

var result = from g in db.Groups
             select new {...};

Подсказка: в хорошо спроектированной базе данных с отношениями вам очень редко нужно использовать ключевое слово join. Вместо этого используйте навигационные свойства.