LINQ的左外连接

如何在不使用join-on-equals-into子句的情况下在C＃LINQ中对对象执行左外部联接？ 有什么办法可以通过where子句做到这一点？ 正确的问题：因为内部联接很容易，我有这样的解决方案

List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key
                             select new JoinPair { LeftId = l.Id, RightId = r.Id})

但是对于左外部联接，我需要一个解决方案。 我的是这样的，但没有用

List< JoinPair> leftFinal = (from l in lefts from r in rights
                             select new JoinPair { 
                                            LeftId = l.Id, 
                                            RightId = ((l.Key==r.Key) ? r.Id : 0
                                        })

JoinPair是一个类：

public class JoinPair { long leftId; long rightId; }

---

#1楼

使用lambda表达式

db.Categories    
  .GroupJoin(
      db.Products,
      Category => Category.CategoryId,
      Product => Product.CategoryId,
      (x, y) => new { Category = x, Products = y })
  .SelectMany(
      xy => xy.Products.DefaultIfEmpty(),
      (x, y) => new { Category = x.Category, Product = y })
  .Select(s => new
  {
      CategoryName = s.Category.Name,     
      ProductName = s.Product.Name   
  })

---

#2楼

通过扩展方法实现左外部联接的实现看起来像

public static IEnumerable<Result> LeftJoin<TOuter, TInner, TKey, Result>(
  this IEnumerable<TOuter> outer, IEnumerable<TInner> inner
  , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector
  , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> comparer)
  {
    if (outer == null)
      throw new ArgumentException("outer");

    if (inner == null)
      throw new ArgumentException("inner");

    if (outerKeySelector == null)
      throw new ArgumentException("outerKeySelector");

    if (innerKeySelector == null)
      throw new ArgumentException("innerKeySelector");

    if (resultSelector == null)
      throw new ArgumentException("resultSelector");

    return LeftJoinImpl(outer, inner, outerKeySelector, innerKeySelector, resultSelector, comparer ?? EqualityComparer<TKey>.Default);
  }

  static IEnumerable<Result> LeftJoinImpl<TOuter, TInner, TKey, Result>(
      IEnumerable<TOuter> outer, IEnumerable<TInner> inner
      , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector
      , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> comparer)
  {
    var innerLookup = inner.ToLookup(innerKeySelector, comparer);

    foreach (var outerElment in outer)
    {
      var outerKey = outerKeySelector(outerElment);
      var innerElements = innerLookup[outerKey];

      if (innerElements.Any())
        foreach (var innerElement in innerElements)
          yield return resultSelector(outerElment, innerElement);
      else
        yield return resultSelector(outerElment, default(TInner));
     }
   }

然后，结果选择器必须处理null元素。 Fx。

   static void Main(string[] args)
   {
     var inner = new[] { Tuple.Create(1, "1"), Tuple.Create(2, "2"), Tuple.Create(3, "3") };
     var outer = new[] { Tuple.Create(1, "11"), Tuple.Create(2, "22") };

     var res = outer.LeftJoin(inner, item => item.Item1, item => item.Item1, (it1, it2) =>
     new { Key = it1.Item1, V1 = it1.Item2, V2 = it2 != null ? it2.Item2 : default(string) });

     foreach (var item in res)
       Console.WriteLine(string.Format("{0}, {1}, {2}", item.Key, item.V1, item.V2));
   }

---

#3楼

如果使用数据库驱动的LINQ提供程序，则可以这样编写可读性更高的左外部联接：

from maintable in Repo.T_Whatever 
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()

如果省略DefaultIfEmpty() ，则将具有内部联接。

采取公认的答案：

  from c in categories
    join p in products on c equals p.Category into ps
    from p in ps.DefaultIfEmpty()

这种语法非常令人困惑，当您想离开联接MULTIPLE表时，不清楚它的工作方式。

注意
应当注意， from alias in Repo.whatever.Where(condition).DefaultIfEmpty()中的from alias in Repo.whatever.Where(condition).DefaultIfEmpty()与外部应用/左连接侧相同，任何（体面的）数据库优化器都可以完美地将其转换为左联接，只要您不引入每行值（又称实际外部适用）。 不要在Linq-2-Objects中执行此操作（因为使用Linq-to-Objects时没有DB优化器）。

详细的例子

var query2 = (
    from users in Repo.T_User
    from mappings in Repo.T_User_Group
         .Where(mapping => mapping.USRGRP_USR == users.USR_ID)
         .DefaultIfEmpty() // <== makes join left join
    from groups in Repo.T_Group
         .Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP)
         .DefaultIfEmpty() // <== makes join left join

    // where users.USR_Name.Contains(keyword)
    // || mappings.USRGRP_USR.Equals(666)  
    // || mappings.USRGRP_USR == 666 
    // || groups.Name.Contains(keyword)

    select new
    {
         UserId = users.USR_ID
        ,UserName = users.USR_User
        ,UserGroupId = groups.ID