c# - 与 Entity Framework Core 的左外连接

Question

我正在尝试使用 EF7 (7.0.0-rc1-final)、vNext RC1 (rc1-final) 和 SQL Server 2014 执行左外连接请求

Database :

Pet: Id, Name

User: Id, Name, #PetId

这个有效:

var queryWorks = from u in _context.Users
                 join p in _context.Pets on u.PetId equals p.Id into pp
                 from p in pp.DefaultIfEmpty()
                 select new {
                     UserName = u.Name,
                     Pet = p
                 };

但是这个不起作用(Message = "Sequence contains no elements"):

var queryFails = from u in _context.Users
                 join p in _context.Pets on u.PetId equals p.Id into pp
                 from p in pp.DefaultIfEmpty()
                 select new {
                     UserName = u.Name,
                     PetName = (p == null ? "NULL" : p.Name)
                 };

SQL Server Profile 2014 显示第二个请求没有发送到 SQL Server。为什么？

Answer 1

我认为你的 p.Name 在你的第二个查询的投影中没有被处理。

从 RC1 开始，EF7 尚不知道如何进行左外连接。简而言之，他们意识到正确行事是一件非常重要的事情，并且他们正在为此努力。

在 issue 3186 中有报道在 github 上，一些开发人员对此发表了评论。

我用另一个有点像你的复制品评论了自己。

合作者“maumar”评论:

Problem is that in Linq (to objects) the concept of LOJ doesn't exist on its own.

Proposed fix is to represent optional navigation using SelectMany-GroupJoin-DefaultIfEmpty combination and then collapse this pattern into LOJ in our relational pipeline. Problem is that this creates much more complex queries (mainly due to introduction of subqueries) and currently breaks for majority of non-trivial cases. Those bugs need to be addressed before we can fix the problem with navigation property expansion.

We do recognize this as a high priority bug, as it potentially returns incorrect results.