实体框架左连接

Question

如何更改此查询以便返回所有u.usergroups？

from u in usergroups 
from p in u.UsergroupPrices 
select new UsergroupPricesList 
{ 
UsergroupID = u.UsergroupID, 
UsergroupName = u.UsergroupName, 
Price = p.Price 
}; 

Answer 1

改编自MSDN，how to left join using EF 4

var query = from u in usergroups 
      join p in UsergroupPrices on u equals p.UsergroupID into gj 
      from x in gj.DefaultIfEmpty() 
      select new { 
       UsergroupID = u.UsergroupID, 
       UsergroupName = u.UsergroupName, 
       Price = (x == null ? String.Empty : x.Price) 
      }; 

Answer 2

这可能是有点矫枉过正，但我​​写了一个扩展方法，这样你就可以使用Join语法（至少在方法做LeftJoin电话符号）：

persons.LeftJoin(
    phoneNumbers, 
    person => person.Id, 
    phoneNumber => phoneNumber.PersonId, 
    (person, phoneNumber) => new 
     { 
      Person = person, 
      PhoneNumber = (phoneNumber != null) ? phoneNumber.Number : null 
     } 
); 

我的代码做什么比加入GroupJoin和SelectMany通话更到当前表达式树。尽管如此，它看起来相当复杂，因为我必须亲自构建表达式并修改用户在resultSelector参数中指定的表达式树，以保持LINQ-to-Entities可转换整个树。

public static class LeftJoinExtension 
{ 
    public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
     this IQueryable<TOuter> outer, 
     IQueryable<TInner> inner, 
     Expression<Func<TOuter, TKey>> outerKeySelector, 
     Expression<Func<TInner, TKey>> innerKeySelector, 
     Expression<Func<TOuter, TInner, TResult>> resultSelector) 
    { 
     MethodInfo groupJoin = typeof (Queryable).GetMethods() 
               .Single(m => m.ToString() == "System.Linq.IQueryable`1[TResult] GroupJoin[TOuter,TInner,TKey,TResult](System.Linq.IQueryable`1[TOuter], System.Collections.Generic.IEnumerable`1[TInner], System.Linq.Expressions.Expression`1[System.Func`2[TOuter,TKey]], System.Linq.Expressions.Expression`1[System.Func`2[TInner,TKey]], System.Linq.Expressions.Expression`1[System.Func`3[TOuter,System.Collections.Generic.IEnumerable`1[TInner],TResult]])") 
               .MakeGenericMethod(typeof (TOuter), typeof (TInner), typeof (TKey), typeof (LeftJoinIntermediate<TOuter, TInner>)); 
     MethodInfo selectMany = typeof (Queryable).GetMethods() 
                .Single(m => m.ToString() == "System.Linq.IQueryable`1[TResult] SelectMany[TSource,TCollection,TResult](System.Linq.IQueryable`1[TSource], System.Linq.Expressions.Expression`1[System.Func`2[TSource,System.Collections.Generic.IEnumerable`1[TCollection]]], System.Linq.Expressions.Expression`1[System.Func`3[TSource,TCollection,TResult]])") 
                .MakeGenericMethod(typeof (LeftJoinIntermediate<TOuter, TInner>), typeof (TInner), typeof (TResult)); 

     var groupJoinResultSelector = (Expression<Func<TOuter, IEnumerable<TInner>, LeftJoinIntermediate<TOuter, TInner>>>) 
             ((oneOuter, manyInners) => new LeftJoinIntermediate<TOuter, TInner> {OneOuter = oneOuter, ManyInners = manyInners}); 

     MethodCallExpression exprGroupJoin = Expression.Call(groupJoin, outer.Expression, inner.Expression, outerKeySelector, innerKeySelector, groupJoinResultSelector); 

     var selectManyCollectionSelector = (Expression<Func<LeftJoinIntermediate<TOuter, TInner>, IEnumerable<TInner>>>) 
              (t => t.ManyInners.DefaultIfEmpty()); 

     ParameterExpression paramUser = resultSelector.Parameters.First(); 

     ParameterExpression paramNew = Expression.Parameter(typeof (LeftJoinIntermediate<TOuter, TInner>), "t"); 
     MemberExpression propExpr = Expression.Property(paramNew, "OneOuter"); 

     LambdaExpression selectManyResultSelector = Expression.Lambda(new Replacer(paramUser, propExpr).Visit(resultSelector.Body), paramNew, resultSelector.Parameters.Skip(1).First()); 

     MethodCallExpression exprSelectMany = Expression.Call(selectMany, exprGroupJoin, selectManyCollectionSelector, selectManyResultSelector); 

     return outer.Provider.CreateQuery<TResult>(exprSelectMany); 
    } 

    private class LeftJoinIntermediate<TOuter, TInner> 
    { 
     public TOuter OneOuter { get; set; } 
     public IEnumerable<TInner> ManyInners { get; set; } 
    } 

    private class Replacer : ExpressionVisitor 
    { 
     private readonly ParameterExpression _oldParam; 
     private readonly Expression _replacement; 

     public Replacer(ParameterExpression oldParam, Expression replacement) 
     { 
      _oldParam = oldParam; 
      _replacement = replacement; 
     } 

     public override Expression Visit(Expression exp) 
     { 
      if (exp == _oldParam) 
      { 
       return _replacement; 
      } 

      return base.Visit(exp); 
     } 
    } 
} 

Answer 3

我可以通过调用主模型上的DefaultIfEmpty（）来做到这一点。这让我留在加盟懒加载实体，似乎更可读的对我说：

 var complaints = db.Complaints.DefaultIfEmpty() 
      .Where(x => x.DateStage1Complete == null || x.DateStage2Complete == null) 
      .OrderBy(x => x.DateEntered) 
      .Select(x => new 
      { 
       ComplaintID = x.ComplaintID, 
       CustomerName = x.Customer.Name, 
       CustomerAddress = x.Customer.Address, 
       MemberName = x.Member != null ? x.Member.Name: string.Empty, 
       AllocationName = x.Allocation != null ? x.Allocation.Name: string.Empty, 
       CategoryName = x.Category != null ? x.Category.Ssl_Name : string.Empty, 
       Stage1Start = x.Stage1StartDate, 
       Stage1Expiry = x.Stage1_ExpiryDate, 
       Stage2Start = x.Stage2StartDate, 
       Stage2Expiry = x.Stage2_ExpiryDate 
      }); 

Answer 4

请让您的生活更轻松（不使用加入到组）：

var query = from ug in UserGroups 
      from ugp in UserGroupPrices.Where(x => x.UserGroupId == ug.Id).DefaultIfEmpty() 
      select new 
      { 
       UserGroupID = ug.UserGroupID, 
       UserGroupName = ug.UserGroupName, 
       Price = ugp.Price 
      };