如何在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; }

当前回答

这是我使用的LeftJoin实现。注意,resultSelector表达式接受2个参数:来自连接两端的一个实例。在我看到的大多数其他实现中,结果选择器只接受一个参数,这是一个具有左/右或外部/内部属性的“连接模型”。我更喜欢这个实现,因为它具有与内置Join方法相同的方法签名。它也适用于IQueryables和EF。

 var results = DbContext.Categories
      .LeftJoin(
            DbContext.Products, c => c.Id, p => p.CategoryId,
            (c, p) => new { Category = c, ProductName = p == null ? "(No Products)" : p.ProductName })
      .ToList();

public static class QueryableExtensions
{
    public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
           this IQueryable<TOuter> outer, 
           IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, 
           Expression<Func<TInner, TKey>> innerKeySelector, 
           Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        var query = outer
            .GroupJoin(inner, outerKeySelector, innerKeySelector, (o, i) => new { o, i })
            .SelectMany(o => o.i.DefaultIfEmpty(), (x, i) => new { x.o, i });
        return ApplySelector(query, x => x.o, x => x.i, resultSelector);
    }

    private static IQueryable<TResult> ApplySelector<TSource, TOuter, TInner, TResult>(
        IQueryable<TSource> source,
        Expression<Func<TSource, TOuter>> outerProperty,
        Expression<Func<TSource, TInner>> innerProperty,
        Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        var p = Expression.Parameter(typeof(TSource), $"param_{Guid.NewGuid()}".Replace("-", string.Empty));
        Expression body = resultSelector?.Body
            .ReplaceParameter(resultSelector.Parameters[0], outerProperty.Body.ReplaceParameter(outerProperty.Parameters[0], p))
            .ReplaceParameter(resultSelector.Parameters[1], innerProperty.Body.ReplaceParameter(innerProperty.Parameters[0], p));
        var selector = Expression.Lambda<Func<TSource, TResult>>(body, p);
        return source.Select(selector);
    }
}

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression source, ParameterExpression toReplace, Expression newExpression)
        => new ReplaceParameterExpressionVisitor(toReplace, newExpression).Visit(source);
}

public class ReplaceParameterExpressionVisitor : ExpressionVisitor
{
    public ReplaceParameterExpressionVisitor(ParameterExpression toReplace, Expression replacement)
    {
        this.ToReplace = toReplace;
        this.Replacement = replacement;
    }

    public ParameterExpression ToReplace { get; }
    public Expression Replacement { get; }
    protected override Expression VisitParameter(ParameterExpression node)
        => (node == ToReplace) ? Replacement : base.VisitParameter(node);
}

其他回答

根据我对类似问题的回答,在这里:

Linq到SQL左外连接使用Lambda语法和连接2列(复合连接键)

获得代码在这里,或克隆我的github回购,并发挥!

查询:

        var petOwners =
            from person in People
            join pet in Pets
            on new
            {
                person.Id,
                person.Age,
            }
            equals new
            {
                pet.Id,
                Age = pet.Age * 2, // owner is twice age of pet
            }
            into pets
            from pet in pets.DefaultIfEmpty()
            select new PetOwner
            {
                Person = person,
                Pet = pet,
            };

λ:

        var petOwners = People.GroupJoin(
            Pets,
            person => new { person.Id, person.Age },
            pet => new { pet.Id, Age = pet.Age * 2 },
            (person, pet) => new
            {
                Person = person,
                Pets = pet,
            }).SelectMany(
            pet => pet.Pets.DefaultIfEmpty(),
            (people, pet) => new
            {
                people.Person,
                Pet = pet,
            });

如“Perform left outer joins”中所述:

var q =
    from c in categories
    join pt in products on c.Category equals pt.Category into ps_jointable
    from p in ps_jointable.DefaultIfEmpty()
    select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName };

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

下面是一个版本的扩展方法解决方案,使用IQueryable代替IEnumerable

public class OuterJoinResult<TLeft, TRight>
{
    public TLeft LeftValue { get; set; }
    public TRight RightValue { get; set; }
}

public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IQueryable<TLeft> left, IQueryable<TRight> right, Expression<Func<TLeft, TKey>> leftKey, Expression<Func<TRight, TKey>> rightKey, Expression<Func<OuterJoinResult<TLeft, TRight>, TResult>> result)
{
    return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
          .SelectMany(o => o.r.DefaultIfEmpty(), (l, r) => new OuterJoinResult<TLeft, TRight> { LeftValue = l.l, RightValue = r })
          .Select(result);
}

有三个表:persons, schools和persons_schools,它将人与他们所学习的学校联系起来。表persons_schools中没有对id=6的person的引用。但是id=6的人显示在左连接的结果网格中。

List<Person> persons = new List<Person>
{
    new Person { id = 1, name = "Alex", phone = "4235234" },
    new Person { id = 2, name = "Bob", phone = "0014352" },
    new Person { id = 3, name = "Sam", phone = "1345" },
    new Person { id = 4, name = "Den", phone = "3453452" },
    new Person { id = 5, name = "Alen", phone = "0353012" },
    new Person { id = 6, name = "Simon", phone = "0353012" }
};

List<School> schools = new List<School>
{
    new School { id = 1, name = "Saint. John's school"},
    new School { id = 2, name = "Public School 200"},
    new School { id = 3, name = "Public School 203"}
};

List<PersonSchool> persons_schools = new List<PersonSchool>
{
    new PersonSchool{id_person = 1, id_school = 1},
    new PersonSchool{id_person = 2, id_school = 2},
    new PersonSchool{id_person = 3, id_school = 3},
    new PersonSchool{id_person = 4, id_school = 1},
    new PersonSchool{id_person = 5, id_school = 2}
    //a relation to the person with id=6 is absent
};

var query = from person in persons
            join person_school in persons_schools on person.id equals person_school.id_person
            into persons_schools_joined
            from person_school_joined in persons_schools_joined.DefaultIfEmpty()
            from school in schools.Where(var_school => person_school_joined == null ? false : var_school.id == person_school_joined.id_school).DefaultIfEmpty()
            select new { Person = person.name, School = school == null ? String.Empty : school.name };

foreach (var elem in query)
{
    System.Console.WriteLine("{0},{1}", elem.Person, elem.School);
}