如何在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; }
我想补充的是,如果您获得MoreLinq扩展,现在支持同质和异构左连接
http://morelinq.github.io/2.8/ref/api/html/Overload_MoreLinq_MoreEnumerable_LeftJoin.htm
例子:
//Pretend a ClientCompany object and an Employee object both have a ClientCompanyID key on them
return DataContext.ClientCompany
.LeftJoin(DataContext.Employees, //Table being joined
company => company.ClientCompanyID, //First key
employee => employee.ClientCompanyID, //Second Key
company => new {company, employee = (Employee)null}, //Result selector when there isn't a match
(company, employee) => new { company, employee }); //Result selector when there is a match
编辑:
回想起来,这可能会起作用,但它将IQueryable转换为IEnumerable,因为morelinq不会将查询转换为SQL。
您可以使用如下所述的GroupJoin: https://stackoverflow.com/a/24273804/4251433
这将确保它仍然是一个IQueryable,以防您以后需要对它进行进一步的逻辑操作。
(from a in db.Assignments
join b in db.Deliveryboys on a.AssignTo equals b.EmployeeId
//from d in eGroup.DefaultIfEmpty()
join c in db.Deliveryboys on a.DeliverTo equals c.EmployeeId into eGroup2
from e in eGroup2.DefaultIfEmpty()
where (a.Collected == false)
select new
{
OrderId = a.OrderId,
DeliveryBoyID = a.AssignTo,
AssignedBoyName = b.Name,
Assigndate = a.Assigndate,
Collected = a.Collected,
CollectedDate = a.CollectedDate,
CollectionBagNo = a.CollectionBagNo,
DeliverTo = e == null ? "Null" : e.Name,
DeliverDate = a.DeliverDate,
DeliverBagNo = a.DeliverBagNo,
Delivered = a.Delivered
});
下面是一个版本的扩展方法解决方案,使用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);
}