IQueryable<TOuter>的扩展方法中提供了 INNER JOIN,GROUP JOIN但是没有提供LEFT JOIN
GROUP JOIN适用于一对多的场景，如果关联的GROUP没有条目，会显示List条目为0，这一点其实也是LEFT join,
但是如果反过来，对于多对一的场景，虽然可以用GROUP JOIN，但是对于单一的条目却还要用List进行包装，就有点逻辑的冗余。
这个时候Left join就派上用场了
/// <summary>
/// InnerJoin
/// </summary>
/// <typeparam name="TInner"></typeparam>
/// <typeparam name="TKey"></typeparam>
/// <typeparam name="TModel"></typeparam>
/// <param name="outerKeySelector"></param>
/// <param name="innerKeySelector"></param>
/// <param name="resultSelector"></param>
/// <returns></returns>
public List<TModel> GetInnerJoin<TInner, TKey, TModel>(
Expression<Func<TEntity, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TEntity, TInner, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().Join(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetLeftJoin<TInner, TKey, TModel>(
Expression<Func<TEntity, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TEntity, TInner, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().LeftOuterJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetLeftJoin<TInner, TKey, TModel>(
Expression<Func<TEntity, bool>> predicate,
Expression<Func<TEntity, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TEntity, TInner, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().Where(predicate).LeftOuterJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetGroupJoin<TInner, TKey, TModel>(
Expression<Func<TEntity, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TEntity, IEnumerable<TInner>, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().GroupJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetGroupJoin<TInner, TKey, TModel>(
Expression<Func<TEntity, bool>> predicate,
Expression<Func<TEntity, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TEntity, IEnumerable<TInner>, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().Where(predicate).GroupJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
}
所以对于 INNER JOIN,LEFT JOIN,GROUP JOIN的使用场景
INNER JOIN适合各种场景，数据平面显示（内连接查询）
Left join适合各种场景，数据平面显示（外连接查询）
GROUP JOIN更适合于一对多，关联项以组的形式包装（外连接查询）
//List<dynamic> list = companyDAL.GetGroupJoin<Product, int, dynamic>(m => m.ID, m => (int)m.CompanyID, (o, i) =>new {o,i });
//List<dynamic> list = productDAL.GetGroupJoin<Company, int, dynamic>( m => (int)m.CompanyID,m => m.ID, (o, i) => new { o, i });
List<dynamic> list = productDAL.GetLeftJoin<Company, dynamic, dynamic>(m => m.ID < , m => new { m.CompanyID, m.Second }, m => new { CompanyID = m.ID, m.Second }, (o, i) => new { o, i });
//List<dynamic> list = productDAL.GetInnerJoin<Company, int, dynamic>(m => (int)m.CompanyID, m => m.ID, (o, i) => new { o, i });
使用dynamic简化泛型参数
/// <summary>
/// InnerJoin
/// </summary>
/// <typeparam name="TInner"></typeparam>
/// <typeparam name="TModel"></typeparam>
/// <param name="outerKeySelector"></param>
/// <param name="innerKeySelector"></param>
/// <param name="resultSelector"></param>
/// <returns></returns>
public List<TModel> GetInnerJoin<TInner, TModel>(
Expression<Func<TEntity, dynamic>> outerKeySelector,
Expression<Func<TInner, dynamic>> innerKeySelector,
Expression<Func<TEntity, TInner, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().Join(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetLeftJoin<TInner, TModel>(
Expression<Func<TEntity, dynamic>> outerKeySelector,
Expression<Func<TInner, dynamic>> innerKeySelector,
Expression<Func<TEntity, TInner, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().LeftOuterJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetLeftJoin<TInner, TModel>(
Expression<Func<TEntity, bool>> predicate,
Expression<Func<TEntity, dynamic>> outerKeySelector,
Expression<Func<TInner, dynamic>> innerKeySelector,
Expression<Func<TEntity, TInner, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().Where(predicate).LeftOuterJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetGroupJoin<TInner, TModel>(
Expression<Func<TEntity, dynamic>> outerKeySelector,
Expression<Func<TInner, dynamic>> innerKeySelector,
Expression<Func<TEntity, IEnumerable<TInner>, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().GroupJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
} public List<TModel> GetGroupJoin<TInner, TModel>(
Expression<Func<TEntity, bool>> predicate,
Expression<Func<TEntity, dynamic>> outerKeySelector,
Expression<Func<TInner, dynamic>> innerKeySelector,
Expression<Func<TEntity, IEnumerable<TInner>, TModel>> resultSelector) where TInner : class
{
var query = dbContext.Set<TEntity>().Where(predicate).GroupJoin(dbContext.Set<TInner>(), outerKeySelector, innerKeySelector, resultSelector);
return query.ToList();
}
扩展方法
public static IQueryable<TResult> LeftOuterJoin<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)
{ // generic methods
var selectManies = typeof(Queryable).GetMethods()
.Where(x => x.Name == "SelectMany" && x.GetParameters().Length == )
.OrderBy(x => x.ToString().Length)
.ToList();
var selectMany = selectManies.First();
var select = typeof(Queryable).GetMethods().First(x => x.Name == "Select" && x.GetParameters().Length == );
var where = typeof(Queryable).GetMethods().First(x => x.Name == "Where" && x.GetParameters().Length == );
var groupJoin = typeof(Queryable).GetMethods().First(x => x.Name == "GroupJoin" && x.GetParameters().Length == );
var defaultIfEmpty = typeof(Queryable).GetMethods().First(x => x.Name == "DefaultIfEmpty" && x.GetParameters().Length == ); // need anonymous type here or let's use Tuple
// prepares for:
// var q2 = Queryable.GroupJoin(db.A, db.B, a => a.Id, b => b.IdA, (a, b) => new { a, groupB = b.DefaultIfEmpty() });
var tuple = typeof(Tuple<,>).MakeGenericType(
typeof(TOuter),
typeof(IQueryable<>).MakeGenericType(
typeof(TInner)
)
);
var paramOuter = Expression.Parameter(typeof(TOuter));
var paramInner = Expression.Parameter(typeof(IEnumerable<TInner>));
var groupJoinExpression = Expression.Call(
null,
groupJoin.MakeGenericMethod(typeof(TOuter), typeof(TInner), typeof(TKey), tuple),
new Expression[]
{
Expression.Constant(outer),
Expression.Constant(inner),
outerKeySelector,
innerKeySelector,
Expression.Lambda(
Expression.New(
tuple.GetConstructor(tuple.GetGenericArguments()),
new Expression[]
{
paramOuter,
Expression.Call(
null,
defaultIfEmpty.MakeGenericMethod(typeof (TInner)),
new Expression[]
{
Expression.Convert(paramInner, typeof (IQueryable<TInner>))
}
)
},
tuple.GetProperties()
),
new[] {paramOuter, paramInner}
)
}
); // prepares for:
// var q3 = Queryable.SelectMany(q2, x => x.groupB, (a, b) => new { a.a, b });
var tuple2 = typeof(Tuple<,>).MakeGenericType(typeof(TOuter), typeof(TInner));
var paramTuple2 = Expression.Parameter(tuple);
var paramInner2 = Expression.Parameter(typeof(TInner));
var paramGroup = Expression.Parameter(tuple);
var selectMany1Result = Expression.Call(
null,
selectMany.MakeGenericMethod(tuple, typeof(TInner), tuple2),
new Expression[]
{
groupJoinExpression,
Expression.Lambda(
Expression.Convert(Expression.MakeMemberAccess(paramGroup, tuple.GetProperty("Item2")),
typeof (IEnumerable<TInner>)),
paramGroup
),
Expression.Lambda(
Expression.New(
tuple2.GetConstructor(tuple2.GetGenericArguments()),
new Expression[]
{
Expression.MakeMemberAccess(paramTuple2, paramTuple2.Type.GetProperty("Item1")),
paramInner2
},
tuple2.GetProperties()
),
new[]
{
paramTuple2,
paramInner2
}
)
}
); // prepares for final step, combine all expressinos together and invoke:
// var q4 = Queryable.SelectMany(db.A, a => q3.Where(x => x.a == a).Select(x => x.b), (a, b) => new { a, b });
var paramTuple3 = Expression.Parameter(tuple2);
var paramTuple4 = Expression.Parameter(tuple2);
var paramOuter3 = Expression.Parameter(typeof(TOuter));
var selectManyResult2 = selectMany
.MakeGenericMethod(
typeof(TOuter),
typeof(TInner),
typeof(TResult)
)
.Invoke(
null,
new object[]
{
outer,
Expression.Lambda(
Expression.Convert(
Expression.Call(
null,
select.MakeGenericMethod(tuple2, typeof(TInner)),
new Expression[]
{
Expression.Call(
null,
where.MakeGenericMethod(tuple2),
new Expression[]
{
selectMany1Result,
Expression.Lambda(
Expression.Equal(
paramOuter3,
Expression.MakeMemberAccess(paramTuple4, paramTuple4.Type.GetProperty("Item1"))
),
paramTuple4
)
}
),
Expression.Lambda(
Expression.MakeMemberAccess(paramTuple3, paramTuple3.Type.GetProperty("Item2")),
paramTuple3
)
}
),
typeof(IEnumerable<TInner>)
),
paramOuter3
),
resultSelector
}
); return (IQueryable<TResult>)selectManyResult2;
}