有次面试时面试官问我EF中用Linq代码怎么写左连接的问题，虽然当时我能想起来怎么写，但是回答的时候磕磕巴巴，有时候还需要多次重复。总的来说，就还是基础不够扎实，用过的次数不多以至于长时间不用就很可能就忘了。

今天就仔细研究了下，权当是做个笔记。

建两张表：

CREATE TABLE [dbo].[Article](
	[Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[CategoryId] [int] NULL,
	[CreateTime] [datetime] NOT NULL
)

CREATE TABLE [dbo].[Category](
	[Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[CategoryName] [nvarchar](50) NOT NULL,
	[Remark] [nvarchar](50) NULL
)

对应到EF中的两个实体：

public class ArticleEntity
{
	public int Id { get; set; }
	public string Title { get; set; }
	public int CategoryId { get; set; }
	public DateTime CreateTime { get; set; }
}
public class CategoryEntity
{
	public int Id { get; set; }
	public string CategoryName { get; set; }
	public int Remark { get; set; }
}

public DbSet<ArticleEntity> Article { get; set; }
public DbSet<CategoryEntity> Category { get; set; }

两个实体的结果集：

var articleQuery = _context.Article.AsQueryable();
var cateQuery = _context.Category.AsQueryable();

INNER JOIN:

Linq写法：

var query = from article in articleQuery
                join cate in cateQuery on article.CategoryId equals cate.Id
                select new { article, cate };

Lambda表达式写法：

var query = articleQuery.Join(cateQuery, a => a.CategoryId, b => b.Id, (a, b) => new
{
    article = a,
    cate = b
});

二者生成的SQL语句均为：

SELECT[a].[Id], [a].[CategoryId], [a].[CreateTime], [a].[Title], [c].[Id], [c].[CategoryName], [c].[Remark]
FROM[Article] AS[a]
INNER JOIN[Category] AS[c] ON[a].[CategoryId] = [c].[Id]

LEFT JOIN:

Linq写法：

var query = from article in articleQuery
                join cate in cateQuery
                on article.CategoryId equals cate.Id into acQuery
                from lj in acQuery.DefaultIfEmpty()
                select new
                {
                    Id = article.Id,
                    Title = article.Title,
                    CreateTime = article.CreateTime,
                    CategoryId = article.CategoryId,
                    CategoryName = lj.CategoryName
                };

Lambda写法：

var query = articleQuery.GroupJoin(cateQuery, a => a.CategoryId, b => b.Id, (a, b) => new
{
    article = a,
    cate = b
}).SelectMany(a => a.cate.DefaultIfEmpty(), (m, n) => new
{
    Id = m.article.Id,
    Title = m.article.Title,
    CreateTime = m.article.CreateTime,
    CategoryId = m.article.CategoryId,
    CategoryName = n.CategoryName
});

二者生成的SQL语句均为：

SELECT[a].[Id], [a].[CategoryId], [c].[CategoryName], [a].[Title], [a].[CreateTime]
FROM[Article] AS[a]
LEFT JOIN[Category] AS[c] ON[a].[CategoryId] = [c].[Id]

RIGHT JOIN:

严格的来说，EF Core中并没有右连接的写法，如果想要实现右连接的效果，可以参考上面的左连接，将左连接的两张表的位置调换一下，即可实现右连接的查询效果

var query = from cate in cateQuery
            join article in articleQuery
            on   cate.Id equals article.CategoryId into acQuery
            from lj in acQuery.DefaultIfEmpty()
            select new
            {
                Id = lj.Id,
                Title = lj.Title,
                CreateTime = lj.CreateTime,
                CategoryId = lj.CategoryId,
                CategoryName = cate.CategoryName
            };