c# - 使用 LINQ 的 IQueryable 左外连接的扩展方法

Question

我正在尝试实现返回类型为 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是一个被用作方法的变量

我发现了一些关于 SO(例如 here)和 CodeProjects 的讨论，但这些讨论适用于 IEnumerable类型不适用于 IQueryable .

Answer 1

简介

这个问题很有意思。问题是函数是委托(delegate)，表达式是 trees ，它们是完全不同的结构。当您使用当前的扩展实现时，它会使用循环并在每个元素的每个步骤上执行您的选择器，并且效果很好。但是当我们谈论 Entity Framework 和 LINQ 时，我们需要树遍历以将其转换为 SQL 查询。所以它比 Funcs 难一点(但我还是喜欢 Expressions)并且存在下面描述的一些问题。

当你想做左外连接时，你可以使用这样的东西(取自这里: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 ));

很好，但不是我们需要的扩展方法。我猜你需要这样的东西:

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 ...
}

创建这样的扩展有很多困难的部分:

• 手动创建复杂的树，编译器在这里帮不了我们
• Where、Select 等方法需要反射
• 匿名类型(!!我们这里需要代码生成？？我希望不需要)

步骤

考虑 2 个简单的表:A(列:Id、Text)和 B(列 Id、IdA、Text)。

外部连接可以通过 3 个步骤实现:

// 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});

代码

好吧，我不是一个很好的出纳员，这是他的代码(抱歉我无法更好地格式化它，但它有效!):

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查询是什么？它可能很大。你猜怎么着？它很小:

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]

希望对您有所帮助。