EF INNER JOIN,LEFT JOIN,GROUP JOIN
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 < 4, 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 == 3) .OrderBy(x => x.ToString().Length) .ToList(); var selectMany = selectManies.First(); var select = typeof(Queryable).GetMethods().First(x => x.Name == "Select" && x.GetParameters().Length == 2); var where = typeof(Queryable).GetMethods().First(x => x.Name == "Where" && x.GetParameters().Length == 2); var groupJoin = typeof(Queryable).GetMethods().First(x => x.Name == "GroupJoin" && x.GetParameters().Length == 5); var defaultIfEmpty = typeof(Queryable).GetMethods().First(x => x.Name == "DefaultIfEmpty" && x.GetParameters().Length == 1); // 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; }
