如何使用linq扩展方法执行左外连接

假设我有一个左外连接:

from f in Foo
join b in Bar on f.Foo_Id equals b.Foo_Id into g
from result in g.DefaultIfEmpty()
select new { Foo = f, Bar = result }

如何使用扩展方法表达相同的任务？例如

Foo.GroupJoin(Bar, f => f.Foo_Id, b => b.Foo_Id, (f,b) => ???)
    .Select(???)

Marc Gravell.. 411

var qry = Foo.GroupJoin(
          Bar, 
          foo => foo.Foo_Id,
          bar => bar.Foo_Id,
          (x,y) => new { Foo = x, Bars = y })
    .SelectMany(
          x => x.Bars.DefaultIfEmpty(),
          (x,y) => new { Foo=x.Foo, Bar=y});

这实际上并不像看起来那么疯狂.基本上`GroupJoin`执行左外连接,只需要`SelectMany`部分,具体取决于你想要选择的内容. (26认同)

这种模式很棒,因为实体框架将它识别为左连接,我以前认为这是不可能的 (6认同)

@nam你需要一个where语句,x.Bar == null (2认同)

@AbdulkarimKanaan是-SelectMany将两对一的多对多拼合成1层，每对有一个条目 (2认同)

---

Ocelot20.. 97

因为这似乎是使用方法(扩展)语法的左外连接的事实上的SO问题,我想我会添加一个替代当前选择的答案(至少在我的经验中)更常见的是我是什么后

// Option 1: Expecting either 0 or 1 matches from the "Right"
// table (Bars in this case):
var qry = Foos.GroupJoin(
          Bars,
          foo => foo.Foo_Id,
          bar => bar.Foo_Id,
          (f,bs) => new { Foo = f, Bar = bs.SingleOrDefault() });

// Option 2: Expecting either 0 or more matches from the "Right" table
// (courtesy of currently selected answer):
var qry = Foos.GroupJoin(
                  Bars, 
                  foo => foo.Foo_Id,
                  bar => bar.Foo_Id,
                  (f,bs) => new { Foo = f, Bars = bs })
              .SelectMany(
                  fooBars => fooBars.Bars.DefaultIfEmpty(),
                  (x,y) => new { Foo = x.Foo, Bar = y });

使用简单的数据集显示差异(假设我们自己加入了值):

List tableA = new List { 1, 2, 3 };
List tableB = new List { 3, 4, 5 };

// Result using both Option 1 and 2. Option 1 would be a better choice
// if we didn't expect multiple matches in tableB.
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3    }

List tableA = new List { 1, 2, 3 };
List tableB = new List { 3, 3, 4 };

// Result using Option 1 would be that an exception gets thrown on
// SingleOrDefault(), but if we use FirstOrDefault() instead to illustrate:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3    } // Misleading, we had multiple matches.
                    // Which 3 should get selected (not arbitrarily the first)?.

// Result using Option 2:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3    }
{ A = 3, B = 3    }    

选项2适用于典型的左外连接定义,但正如我前面提到的,根据数据集的不同,通常会不必要地复杂.

1> Marc Gravell..：

---

var qry = Foo.GroupJoin(
          Bar, 
          foo => foo.Foo_Id,
          bar => bar.Foo_Id,
          (x,y) => new { Foo = x, Bars = y })
    .SelectMany(
          x => x.Bars.DefaultIfEmpty(),
          (x,y) => new { Foo=x.Foo, Bar=y});

---

这实际上并不像看起来那么疯狂.基本上`GroupJoin`执行左外连接,只需要`SelectMany`部分,具体取决于你想要选择的内容.

---

这种模式很棒,因为实体框架将它识别为左连接,我以前认为这是不可能的

---

@nam你需要一个where语句,x.Bar == null

---

@AbdulkarimKanaan是-SelectMany将两对一的多对多拼合成1层，每对有一个条目

2> Ocelot20..：

---

因为这似乎是使用方法(扩展)语法的左外连接的事实上的SO问题,我想我会添加一个替代当前选择的答案(至少在我的经验中)更常见的是我是什么后

// Option 1: Expecting either 0 or 1 matches from the "Right"
// table (Bars in this case):
var qry = Foos.GroupJoin(
          Bars,
          foo => foo.Foo_Id,
          bar => bar.Foo_Id,
          (f,bs) => new { Foo = f, Bar = bs.SingleOrDefault() });

// Option 2: Expecting either 0 or more matches from the "Right" table
// (courtesy of currently selected answer):
var qry = Foos.GroupJoin(
                  Bars, 
                  foo => foo.Foo_Id,
                  bar => bar.Foo_Id,
                  (f,bs) => new { Foo = f, Bars = bs })
              .SelectMany(
                  fooBars => fooBars.Bars.DefaultIfEmpty(),
                  (x,y) => new { Foo = x.Foo, Bar = y });

使用简单的数据集显示差异(假设我们自己加入了值):

List tableA = new List { 1, 2, 3 };
List tableB = new List { 3, 4, 5 };

// Result using both Option 1 and 2. Option 1 would be a better choice
// if we didn't expect multiple matches in tableB.
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3    }

List tableA = new List { 1, 2, 3 };
List tableB = new List { 3, 3, 4 };

// Result using Option 1 would be that an exception gets thrown on
// SingleOrDefault(), but if we use FirstOrDefault() instead to illustrate:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3    } // Misleading, we had multiple matches.
                    // Which 3 should get selected (not arbitrarily the first)?.

// Result using Option 2:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3    }
{ A = 3, B = 3    }    

选项2适用于典型的左外连接定义,但正如我前面提到的,根据数据集的不同,通常会不必要地复杂.

---

我认为如果你有另外一个Join或Include,"bs.SingleOrDefault()"将不起作用.在这种情况下我们需要"bs.FirstOrDefault()".

---

确实,实体框架和Linq to SQL都要求,因为他们不能轻易地在连接中进行"单一"检查.然而,"SingleOrDefault"是一种更"正确"的方式来演示这个IMO.

---

这不是问题中未指明的"额外要求",这是很多人在说"左外加"时所想到的.此外,Dherik引用的FirstOrDefault要求是EF/L2SQL行为,而不是L2Object(这些都不在标签中).在这种情况下,SingleOrDefault绝对是正确的调用方法.当然,如果您遇到的记录多于数据集的记录而不是选择任意记录并导致令人困惑的未定义结果,那么您希望抛出异常.

3> 小智..：

---

组连接方法不需要实现两个数据集的连接.

内部联接:

var qry = Foos.SelectMany
            (
                foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id),
                (foo, bar) => new
                    {
                    Foo = foo,
                    Bar = bar
                    }
            );

对于Left Join,只需添加DefaultIfEmpty()

var qry = Foos.SelectMany
            (
                foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id).DefaultIfEmpty(),
                (foo, bar) => new
                    {
                    Foo = foo,
                    Bar = bar
                    }
            );

EF正确转换为SQL.对于LINQ to对象,使用GroupJoin加入它是更好的,因为它在内部使用Lookup,但是如果你正在查询DB,那么跳过GroupJoin就是AFAIK的性能.

与GroupJoin()相比,这种方式的Personlay更具可读性.SelectMany()

4> hajirazin..：

---

您可以创建扩展方法,如:

public static IEnumerable LeftOuterJoin(this IEnumerable source, IEnumerable other, Func func, Func innerkey, Func res)
    {
        return from f in source
               join b in other on func.Invoke(f) equals innerkey.Invoke(b) into g
               from result in g.DefaultIfEmpty()
               select res.Invoke(f, result);
    }