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

其他回答

下面是一个例子,如果你需要连接2个以上的表:

from d in context.dc_tpatient_bookingd
join bookingm in context.dc_tpatient_bookingm 
     on d.bookingid equals bookingm.bookingid into bookingmGroup
from m in bookingmGroup.DefaultIfEmpty()
join patient in dc_tpatient
     on m.prid equals patient.prid into patientGroup
from p in patientGroup.DefaultIfEmpty()

裁判:https://stackoverflow.com/a/17142392/2343

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

这是我使用的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);
}

下面是使用方法语法的一个相当容易理解的版本:

IEnumerable<JoinPair> outerLeft =
    lefts.SelectMany(l => 
        rights.Where(r => l.Key == r.Key)
              .DefaultIfEmpty(new Item())
              .Select(r => new JoinPair { LeftId = l.Id, RightId = r.Id }));
class Program
{
    List<Employee> listOfEmp = new List<Employee>();
    List<Department> listOfDepart = new List<Department>();

    public Program()
    {
        listOfDepart = new List<Department>(){
            new Department { Id = 1, DeptName = "DEV" },
            new Department { Id = 2, DeptName = "QA" },
            new Department { Id = 3, DeptName = "BUILD" },
            new Department { Id = 4, DeptName = "SIT" }
        };


        listOfEmp = new List<Employee>(){
            new Employee { Empid = 1, Name = "Manikandan",DepartmentId=1 },
            new Employee { Empid = 2, Name = "Manoj" ,DepartmentId=1},
            new Employee { Empid = 3, Name = "Yokesh" ,DepartmentId=0},
            new Employee { Empid = 3, Name = "Purusotham",DepartmentId=0}
        };

    }
    static void Main(string[] args)
    {
        Program ob = new Program();
        ob.LeftJoin();
        Console.ReadLine();
    }

    private void LeftJoin()
    {
        listOfEmp.GroupJoin(listOfDepart.DefaultIfEmpty(), x => x.DepartmentId, y => y.Id, (x, y) => new { EmpId = x.Empid, EmpName = x.Name, Dpt = y.FirstOrDefault() != null ? y.FirstOrDefault().DeptName : null }).ToList().ForEach
            (z =>
            {
                Console.WriteLine("Empid:{0} EmpName:{1} Dept:{2}", z.EmpId, z.EmpName, z.Dpt);
            });
    }
}

class Employee
{
    public int Empid { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

class Department
{
    public int Id { get; set; }
    public string DeptName { get; set; }
}

输出