如何在c# LINQ中执行左外连接到对象而不使用join-on-equal -into子句?有办法用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; }

当前回答

简单的方法是使用Let关键字。这对我很有用。

from AItem in Db.A
Let BItem = Db.B.Where(x => x.id == AItem.id ).FirstOrDefault() 
Where SomeCondition
Select new YourViewModel
{
    X1 = AItem.a,
    X2 = AItem.b,
    X3 = BItem.c
}

这是一个模拟左连接。如果B表中的每一项与A表中的每一项都不匹配,BItem返回null

其他回答

使用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   
  });

概述:在这个代码片段中,我演示了如何按ID分组,其中Table1和Table2具有一对多关系。我集中在 Id, Field1和Field2。如果需要第三次查找表,并且需要左连接关系,那么子查询是有用的。 我展示了一个左连接分组和一个子查询linq。结果是等价的。

class MyView
{
public integer Id {get,set};
    public String Field1  {get;set;}
public String Field2 {get;set;}
    public String SubQueryName {get;set;}                           
}

IList<MyView> list = await (from ci in _dbContext.Table1
                                               join cii in _dbContext.Table2
                                                   on ci.Id equals cii.Id

                                               where ci.Field1 == criterion
                                               group new
                                               {
                                                   ci.Id
                                               } by new { ci.Id, cii.Field1, ci.Field2}

                                           into pg
                                               select new MyView
                                               {
                                                   Id = pg.Key.Id,
                                                   Field1 = pg.Key.Field1,
                                                   Field2 = pg.Key.Field2,
                                                   SubQueryName=
                                                   (from chv in _dbContext.Table3 where chv.Id==pg.Key.Id select chv.Field1).FirstOrDefault()
                                               }).ToListAsync<MyView>();


 Compared to using a Left Join and Group new

IList<MyView> list = await (from ci in _dbContext.Table1
                                               join cii in _dbContext.Table2
                                                   on ci.Id equals cii.Id

                       join chv in _dbContext.Table3
                                                  on cii.Id equals chv.Id into lf_chv
                                                from chv in lf_chv.DefaultIfEmpty()

                                               where ci.Field1 == criterion
                                               group new
                                               {
                                                   ci.Id
                                               } by new { ci.Id, cii.Field1, ci.Field2, chv.FieldValue}

                                           into pg
                                               select new MyView
                                               {
                                                   Id = pg.Key.Id,
                                                   Field1 = pg.Key.Field1,
                                                   Field2 = pg.Key.Field2,
                                                   SubQueryName=pg.Key.FieldValue
                                               }).ToListAsync<MyView>();

扩展方法,类似于使用join语法的左连接

public static class LinQExtensions
{
    public static IEnumerable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, 
        Func<TOuter, TKey> outerKeySelector, 
        Func<TInner, TKey> innerKeySelector, 
        Func<TOuter, TInner, TResult> resultSelector)
    {
        return outer.GroupJoin(
            inner, 
            outerKeySelector, 
            innerKeySelector,
            (outerElement, innerElements) => resultSelector(outerElement, innerElements.FirstOrDefault()));
    }
}

我刚刚在。net内核中编写了它,它看起来像预期的那样工作。

小测试:

        var Ids = new List<int> { 1, 2, 3, 4};
        var items = new List<Tuple<int, string>>
        {
            new Tuple<int, string>(1,"a"),
            new Tuple<int, string>(2,"b"),
            new Tuple<int, string>(4,"d"),
            new Tuple<int, string>(5,"e"),
        };

        var result = Ids.LeftJoin(
            items,
            id => id,
            item => item.Item1,
            (id, item) => item ?? new Tuple<int, string>(id, "not found"));

        result.ToList()
        Count = 4
        [0]: {(1, a)}
        [1]: {(2, b)}
        [2]: {(3, not found)}
        [3]: {(4, d)}

这是一种SQL语法,与用于内连接和左外连接的LINQ语法相比。 左外连接:

http://www.ozkary.com/2011/07/linq-to-entity-inner-and-left-joins.html

下面的示例在产品和类别之间进行组连接。这本质上是左连接。即使类别表为空,into表达式也会返回数据。要访问类别表的属性,现在必须通过在catList.DefaultIfEmpty()语句中添加from cl从可枚举结果中进行选择。

现在作为一个扩展方法:

public static class LinqExt
{
    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IEnumerable<TLeft> left, IEnumerable<TRight> right, Func<TLeft, TKey> leftKey, Func<TRight, TKey> rightKey,
        Func<TLeft, TRight, TResult> result)
    {
        return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
             .SelectMany(
                 o => o.r.DefaultIfEmpty(),
                 (l, r) => new { lft= l.l, rght = r })
             .Select(o => result.Invoke(o.lft, o.rght));
    }
}

像平常使用join一样使用:

var contents = list.LeftOuterJoin(list2, 
             l => l.country, 
             r => r.name,
            (l, r) => new { count = l.Count(), l.country, l.reason, r.people })

希望这能为您节省一些时间。