EF 10 LeftJoin & RightJoin 的支持

Intro

我们前面介绍 Linq LeftJoin/RightJoin 的时候提到过 EF Core 也会支持 LeftJoin/RightJoin，EF Core 在 preview 1 的时候支持了 LeftJoin 在 preview 2 中支持了 RightJoin，现在 preview 2 已经发布我们一起来看下吧

Sample

测试示例如下：

public static async Task EFLeftRightJoinSample()
{
 var services = new ServiceCollection();
 services.AddSqlite<TestDbContext>("Data Source=test.db", optionsAction: options =>
 {
 options.LogTo(Console.WriteLine);
 });
 awaitusingvar serviceProvider = services.BuildServiceProvider();

 usingvar scope = serviceProvider.CreateScope();
 var dbContext = scope.ServiceProvider.GetRequiredService<TestDbContext>();
 await dbContext.Database.EnsureDeletedAsync();
 await dbContext.Database.EnsureCreatedAsync();

 dbContext.Jobs.Add(new Job() { Id = 1, Name = "test" });
 dbContext.Employees.Add(new Employee() { Id = 1, JobId = 1, Name = "Alice" });
 dbContext.Employees.Add(new Employee() { Id = 2, JobId = 2, Name = "Jane" });
 await dbContext.SaveChangesAsync();

 var result = await dbContext.Employees.AsNoTracking()
 // ReSharper disable once EntityFramework.UnsupportedServerSideFunctionCall
 .LeftJoin(dbContext.Jobs.AsNoTracking(), e => e.JobId, j => j.Id, (e, j) => new
 {
 e.Id,
 e.Name,
 e.JobId,
 JobName = j ==  ? "Unknown" : j.Name
 })
 .ToArrayAsync();
 foreach (var item in result)
 {
 Console.WriteLine(JsonSerializer.Serialize(item));
 }

 result = await dbContext.Jobs.AsNoTracking()
 // ReSharper disable once EntityFramework.UnsupportedServerSideFunctionCall
 .RightJoin(dbContext.Employees.AsNoTracking(), j => j.Id, e => e.JobId, (j, e) => new
 {
 e.Id,
 e.Name,
 e.JobId,
 JobName = j ==  ? "Unknown" : j.Name
 })
 .ToArrayAsync();
 foreach (var item in result)
 {
 Console.WriteLine(JsonSerializer.Serialize(item));
 }
}

为了方便看测试结果我们将 Log 输出到 Console 里方便打印实际数据库的查询是否符合预期

从上面执行的 SQL 可以看得出来我们的 LeftJoin & RightJoin 被正确翻译成了 SQL

大家留意代码的话会发现有一行注释

// ReSharper disable once EntityFramework.UnsupportedServerSideFunctionCall

这行注释是给 Resharper 和 Rider 的，不加的话目前会有一个 warning，示例如下：

在之前的版本里不能转成 SQL 但从 EF Core 10 开始就可以了，ReSharper 后续应该会有所改进避免对 LeftJoin/RightJoin 产生 warning 吧

References

• https://github.com/dotnet/efcore/issues/35379
• https://github.com/dotnet/efcore/issues/12793
• https://github.com/dotnet/efcore/pull/35451
• https://github.com/dotnet/efcore/issues/35367
• https://github.com/dotnet/efcore/pull/35462
• https://github.com/WeihanLi/SamplesInPractice/blob/main/net10sample/Net10Samples/LinqSamples.cs#L74