使用LINQ的IQueryable左外部联接的扩展方法 [英] Extension method for IQueryable left outer join using LINQ
问题描述
我正在尝试使用返回类型IQueryable实现左外部联接扩展方法.
I am trying to implement Left outer join extension method with return type IQueryable.
我写的函数如下
public static IQueryable<TResult> LeftOuterJoin2<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer,
IQueryable<TInner> inner,
Func<TOuter, TKey> outerKeySelector,
Func<TInner, TKey> innerKeySelector,
Func<TOuter, TInner, TResult> resultSelector)
{
return
from outerItem in outer
join innerItem in inner on outerKeySelector(outerItem)
equals innerKeySelector(innerItem) into joinedData
from r in joinedData.DefaultIfEmpty()
select resultSelector(outerItem, r);
}
它无法生成查询.原因可能是:我使用了Func<>而不是Expression<>.我也尝试了Expression<>.它在outerKeySelector(outerItem)行上给我一个错误，这是outerKeySelector是用作方法的变量
It can't generate the query. The reason might be: I have used Func<> instead of Expression<>. I tried with Expression<> as well. It gives me an error on outerKeySelector(outerItem) line, which is outerKeySelector is a variable which is being used as a method
I found some discussions on SO (such as here) and CodeProjects, but those work for IEnumerable types not for IQueryable.
推荐答案
简介
这个问题很有趣.问题是Funcs是委托，而Expressions是树，它们是完全不同的结构.当您使用当前的扩展实现时，它会使用循环并在每个步骤的每个元素上执行选择器，并且效果很好.但是，当我们谈论实体框架和LINQ时，我们需要遍历树以将其转换为SQL查询.因此，这比Funcs难小"(但我还是喜欢Expressions)，并且在下面描述了一些问题.
This question is very interesting. The problem is Funcs are delegates and Expressions are trees, they are completely different structures. When you use your current extension implementation it uses loops and executes your selectors on each step for each element and it works well. But when we talk about entity framework and LINQ we need tree traversal for translation it to SQL query. So it's a "little" harder than Funcs (but I like Expressions anyway) and there are some problems described below.
当您想进行左外部连接时，可以使用如下所示的内容(从此处获取:
When you want to do left outer join you can use something like this (taken from here: How to implement left join in JOIN Extension method)
var leftJoin = p.Person.Where(n => n.FirstName.Contains("a"))
.GroupJoin(p.PersonInfo,
n => n.PersonId,
m => m.PersonId,
(n, ms) => new { n, ms = ms.DefaultIfEmpty() })
.SelectMany(z => z.ms.Select(m => new { n = z.n, m ));
这很好，但这不是我们需要的扩展方法.我猜你需要这样的东西:
It is good, but it is not extension method we need. I guess you need something like this:
using (var db = new Database1Entities("..."))
{
var my = db.A.LeftOuterJoin2(db.B, a => a.Id, b => b.IdA,
(a, b) => new { a, b, hello = "Hello World!" });
// other actions ...
}
创建此类扩展有很多困难的部分:
There are many hard parts in creating such extensions:
- 手动创建复杂树，编译器在这里无济于事
-
Where，Select等方法需要反射 - 匿名类型(!!我们在这里需要代码生成?我希望不要)
- Creating complex trees manually, compiler will not help us here
- Reflection is needed for methods like
Where,Select, etc - Anonymous types (!! we need codegen here?? I hope no)
步骤
考虑2个简单的表:A(列:Id，文本)和B(列ID，IdA，文本).
Consider 2 simple tables: A (columns: Id, Text) and B (Columns Id, IdA, Text).
外部连接可以通过3个步骤实现:
Outer join could be implemented in 3 steps:
// group join as usual + use DefaultIfEmpty
var q1 = Queryable.GroupJoin(db.A, db.B, a => a.Id, b => b.IdA,
(a, b) => new { a, groupB = b.DefaultIfEmpty() });
// regroup data to associated list a -> b, it is usable already, but it's
// impossible to use resultSelector on this stage,
// beacuse of type difference (quite deep problem: some anonymous type != TOuter)
var q2 = Queryable.SelectMany(q1, x => x.groupB, (a, b) => new { a.a, b });
// second regroup to get the right types
var q3 = Queryable.SelectMany(db.A,
a => q2.Where(x => x.a == a).Select(x => x.b),
(a, b) => new {a, b});
代码
好吧，我不是一个很好的出纳员，这是我的代码(对不起，我无法对其进行更好的格式化，但是有效！)
Ok, I'm not such a good teller, here is he code I have (Sorry I was unable to format it better, but it works!):
public static IQueryable<TResult> LeftOuterJoin2<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;
}
用法
又是用法:
db.A.LeftOuterJoin2(db.B, a => a.Id, b => b.IdA,
(a, b) => new { a, b, hello = "Hello World!" });
看着这个，您可以想到所有这一切的sql查询是什么?可能很大.你猜怎么了?它很小:
Looking at this you can think what is the sql query for all this? It might be huge. Guess what? It's quite small:
SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Extent1].[Text] AS [Text],
[Join1].[Id1] AS [Id1],
[Join1].[IdA] AS [IdA],
[Join1].[Text2] AS [Text2],
N'Hello World!' AS [C2]
FROM [A] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Id] AS [Id2], [Extent2].[Text] AS [Text], [Extent3].[Id] AS [Id1], [Extent3].[IdA] AS [IdA], [Extent3].[Text2] AS [Text2]
FROM [A] AS [Extent2]
LEFT OUTER JOIN [B] AS [Extent3] ON [Extent2].[Id] = [Extent3].[IdA] ) AS [Join1] ON [Extent1].[Id] = [Join1].[Id2]
希望它会有所帮助.
这篇关于使用LINQ的IQueryable左外部联接的扩展方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
