联接

联接

借助 LINQ Join 运算符，可根据每个源的键选择器连接两个数据源，并在键匹配时生成值的元组。该运算符在关系数据库中自然而然地转换为 INNER JOIN。虽然 LINQ Join 具有外部和内部键选择器，但数据库只需要一个联接条件。因此 EF Core 通过比较外部键选择器和内部键选择器是否相等，来生成联接条件。此外，如果键选择器是匿名类型，则 EF Core 会生成一个联接条件以用于比较组件是否相等。

var query = from photo in context.Set<PersonPhoto>()
            join person in context.Set<Person>()
            on photo.PersonPhotoId equals person.PhotoId
            select new { person, photo };Code language: JavaScript (javascript)

SELECT [p].[PersonId], [p].[Name], [p].[PhotoId], [p0].[PersonPhotoId], [p0].[Caption], [p0].[Photo]
FROM [PersonPhoto] AS [p0]
INNER JOIN [Person] AS [p] ON [p0].[PersonPhotoId] = [p].[PhotoId]Code language: PHP (php)

GroupJoin

var query = from b in context.Set<Blog>()
            join p in context.Set<Post>()
            on b.BlogId equals p.PostId into grouping
            select new { b, grouping };Code language: JavaScript (javascript)

var query = from b in context.Set<Blog>()
            join p in context.Set<Post>()
                on b.BlogId equals p.PostId into grouping
            select new { b, Posts = grouping.Where(p => p.Content.Contains("EF")).ToList() };Code language: JavaScript (javascript)

集合选择器不引用外部

如果集合选择器不引用外部源中的任何内容，则结果是这两个数据源的笛卡尔乘积。它在关系数据库中转换为 CROSS JOIN。

var query = from b in context.Set<Blog>()
            from p in context.Set<Post>()
            select new { b, p };Code language: JavaScript (javascript)

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
CROSS JOIN [Posts] AS [p]Code language: CSS (css)

集合选择器引用 where 子句中的外部

如果集合选择器具有引用外部元素的 where 子句，则 EF Core 会将其转换为数据库联接，并将谓词用作联接条件。在对外部元素使用集合导航作为集合选择器时，通常会出现这种情况。如果外部元素的集合为空，则不会为该外部元素生成任何结果。

var query = from b in context.Set<Blog>()
            from p in context.Set<Post>().Where(p => b.BlogId == p.BlogId)
            select new { b, p };Code language: JavaScript (javascript)

var query2 = from b in context.Set<Blog>()
             from p in context.Set<Post>().Where(p => b.BlogId == p.BlogId).DefaultIfEmpty()
             select new { b, p };Code language: JavaScript (javascript)

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
INNER JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]Code language: PHP (php)

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]Code language: PHP (php)

集合选择器引用非 where 情况下的外部

var query = from b in context.Set<Blog>()
            from p in context.Set<Post>().Select(p => b.Url + "=>" + p.Title)
            select new { b, p };Code language: JavaScript (javascript)

var query2 = from b in context.Set<Blog>()
             from p in context.Set<Post>().Select(p => b.Url + "=>" + p.Title).DefaultIfEmpty()
             select new { b, p };Code language: JavaScript (javascript)

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], ([b].[Url] + N'=>') + [p].[Title] AS [p]
FROM [Blogs] AS [b]
CROSS APPLY [Posts] AS [p]Code language: PHP (php)

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], ([b].[Url] + N'=>') + [p].[Title] AS [p]
FROM [Blogs] AS [b]
OUTER APPLY [Posts] AS [p]Code language: PHP (php)

GroupBy

var query = from p in context.Set<Post>()
            group p by p.AuthorId into g
            select new
            {
                g.Key,
                Count = g.Count()
            };Code language: JavaScript (javascript)

SELECT [p].[AuthorId] AS [Key], COUNT(*) AS [Count]
FROM [Posts] AS [p]
GROUP BY [p].[AuthorId]Code language: CSS (css)

var query = from p in context.Set<Post>()
            group p by p.AuthorId into g
            where g.Count() > 0
            orderby g.Key
            select new
            {
                g.Key,
                Count = g.Count()
            };Code language: JavaScript (javascript)

SELECT [p].[AuthorId] AS [Key], COUNT(*) AS [Count]
FROM [Posts] AS [p]
GROUP BY [p].[AuthorId]
HAVING COUNT(*) > 0
ORDER BY [p].[AuthorId]Code language: CSS (css)

EF Core 支持的聚合运算符如下所示：

• Avg
• Count
• LongCount
• Max
• Min
• Sum

Left Join

虽然 Left Join 不是 LINQ 运算符，但关系数据库具有常用于查询的 Left Join 的概念。LINQ 查询中的特定模式提供与服务器上的 LEFT JOIN 相同的结果。

var query = from b in context.Set<Blog>()
            join p in context.Set<Post>()
                on b.BlogId equals p.BlogId into grouping
            from p in grouping.DefaultIfEmpty()
            select new { b, p };Code language: JavaScript (javascript)

结果示例：

a	b
1	1
2	null
3	3

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url], [p].[PostId], [p].[AuthorId], [p].[BlogId], [p].[Content], [p].[Rating], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]Code language: PHP (php)