LINQ到实体扩展方法内部查询（EF6） [英] Linq to entities extension method inner query (EF6)

问题描述

有人能向我解释为什么EF引擎在以下情况下失败

Can someone explain to me why the EF Engine is failing in the following scenario?

它正常工作与下面的表达式：

It works fine with the following expression:

var data = context.Programs
    .Select(d => new MyDataDto
    {
        ProgramId = d.ProgramId,
        ProgramName = d.ProgramName,
        ClientId = d.ClientId,
        Protocols = d.Protocols.Where(p => p.UserProtocols.Any(u => u.UserId == userId))
                .Count(pr => pr.Programs.Any(pg => pg.ProgramId == d.ProgramId))
    })
    .ToList();

但是，如果我封装了一些成一个扩展方法：

But if I encapsulate some into an extension method:

public static IQueryable<Protocol> ForUser(this IQueryable<Protocol> protocols, int userId)
{
    return protocols.Where(p => p.UserProtocols.Any(u => u.UserId == userId));
}

结果查询：

The resulting query:

var data = context.Programs
    .Select(d => new MyDataDto
    {
        ProgramId = d.ProgramId,
        ProgramName = d.ProgramName,
        ClientId = d.ClientId,
        Protocols = d.Protocols.ForUser(userId)
                .Count(pr => pr.Programs.Any(pg => pg.ProgramId == d.ProgramId))
    })
    .ToList();

与该异常失败：LINQ到实体无法识别方法System.Linq.IQueryable1 [DAL .Protocol] ForUser（System.Linq.IQueryable1 [DAL.Protocol]，Int32）将法，这种方法不能被翻译成店的表情。

Fails with the exception: LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[DAL.Protocol] ForUser(System.Linq.IQueryable1[DAL.Protocol], Int32)' method, and this method cannot be translated into a store expression.

我期望在EF引擎来构建整个表达式树，链接必要的表达，然后生成SQL。为什么没有这样做呢？

I would expect the EF Engine to build the entire expression tree, chaining the necessary expressions and then generate the SQL. Why doesn't it do that?

推荐答案

这是发生因为调用 ForUser（）正在当它看到你传递到选择的lambda的C＃编译器生成的表达式树内进行。实体框架试图弄清楚如何该函数转换成SQL，但它不能调用函数的几个原因（如 d.Protocols 不此刻存在）。

This is happening because the call to ForUser() is being made inside of the expression tree that the C# compiler builds when it sees the lambda you pass into Select. Entity Framework tries to figure out how to convert that function into SQL, but it can't invoke the function for a few reasons (e.g. d.Protocols does not exist at the moment).

这是一个情况下是这样的是让你的助手返回一个标准的lambda表达式，然后传递到最简单的方法在 。凡（）方法自己：

The simplest approach that works for a case like this is to have your helper return a criteria lambda expression, and then pass that into the .Where() method yourself:

public static Expression<Func<Protocol, true>> ProtocolIsForUser(int userId)
{
    return p => p.UserProtocols.Any(u => u.UserId == userId);
}

...

...

var protocolCriteria = Helpers.ProtocolIsForUser(userId);
var data = context.Programs
    .Select(d => new MyDataDto
    {
        ProgramId = d.ProgramId,
        ProgramName = d.ProgramName,
        ClientId = d.ClientId,
        Protocols = d.Protocols.Count(protocolCriteria)
    })
    .ToList();

更多信息

当你调用一个表达式树之外LINQ的方法（比如你做context.Programs.Select（...））的 Queryable.Select（）扩展方法实际上被调用，它的实现返回一个的IQueryable<> 表示扩展方法获取调用原始 IQueryable的<> 。在这里，我们选择的实现，例如：

More information

When you invoke a LINQ method outside of an expression tree (like you do with context.Programs.Select(...)), the Queryable.Select() extension method actually gets invoked, and its implementation returns an IQueryable<> that represents the extension method getting called on the original IQueryable<>. Here's the implementation of Select, for instance:

    public static IQueryable<TResult> Select<TSource,TResult>(this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector) {
        if (source == null)
            throw Error.ArgumentNull("source");
        if (selector == null)
            throw Error.ArgumentNull("selector");
        return source.Provider.CreateQuery<TResult>( 
            Expression.Call(
                null,
                GetMethodInfo(Queryable.Select, source, selector),
                new Expression[] { source.Expression, Expression.Quote(selector) }
                ));
    }

当可查询的提供者必须从 IQueryable的<> ，分析了表达式树，并试图找出如何解释这些方法调用。实体框架拥有许多LINQ相关功能的如。凡（）和。选择（），因此它知道如何根据这些方法调用到SQL。但是，它不知道该怎么为你写的方法做的。

When the queryable's Provider has to generate actual data from the IQueryable<>, it analyzes the expression tree and tries to figure out how to interpret those method calls. Entity Framework has built-in knowledge of many LINQ-related functions like .Where() and .Select(), so it knows how to translate those method calls into SQL. However, it doesn't know what to do for methods that you write.

那么，为什么这项工作？

So why does this work?

var data = context.Programs.ForUser(userId);

答案是，你的 ForUser 方法没有实现像选择上述方法：您不添加一个表达式的可查询的代表呼吁 ForUser 。相反，在返回。凡（）调用的结果。从的IQueryable<> 的角度来看，就好像其中（）直接调用，调用 ForUser（）从来没有发生过。

The answer is that your ForUser method is not implemented like the Select method above: you are not adding an expression to the queryable to represent calling ForUser. Instead, you are returning the result of a .Where() call. From the IQueryable<>'s perspective, it's as if Where() was called directly, and the call to ForUser() never happened.

您可以通过捕获表达的属性的IQueryable<> ：

You can prove this by capturing the Expression property on the IQueryable<>:

Console.WriteLine(data.Expression.ToString());

...这将产生这样的：

... which will produce something like this:

Programs.Where（U =>（u.UserId ==值（助手<> c__DisplayClass1_0）.userId））

有对没有呼叫ForUser（）在表达式中的任何地方。

There's no call to ForUser() anywhere in that expression.

在另一方面，如果包括 ForUser（）调用一个表达式树像这样的内部：

On the other hand, if you include the ForUser() call inside of an expression tree like this:

var data = context.Programs.Select(d => d.Protocols.ForUser(id));

...那么 .ForUser（）方法实际上从未被调用，所以它永远不会返回的IQueryable<> ，知道了。凡（）方法接到电话。相反，在可查询的显示 .ForUser（）获取调用的表达式树的。输出其表达式树会是这个样子：

... then the .ForUser() method never actually gets invoked, so it never returns an IQueryable<> that knows the .Where() method got called. Instead, the expression tree for the queryable shows .ForUser() getting invoked. Outputting its expression tree would look something like this:

Programs.Select（D => d.Protocols。 ForUser（值（库<> c__DisplayClass1_0）.userId））

实体框架不知道是什么 ForUser（）是应该做的。至于我们关心的，你可以写 ForUser（）做一些事情，是不可能在SQL做。所以，它会告诉你，这不是一个支持的方法。

Entity Framework has no idea what ForUser() is supposed to do. As far as it's concerned, you could have written ForUser() to do something that's impossible to do in SQL. So it tells you that's not a supported method.

这篇关于LINQ到实体扩展方法内部查询（EF6）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！