在Entity Framework Query中使用C＃函数

In my C# code, I have 2 WHERE queries both of which I can call on an IQueryable and have the whole thing compiled down to SQL, and both of which have a great deal of common logic.

在我的C＃代码中，我有2个WHERE查询，我可以在IQueryable上调用它们，并将整个事务编译为SQL，并且这两个查询都有很多共同的逻辑。

I believe this is NOT a duplication of this similar question: Using Function in Select Clause of Entity Framework Query because in my scenario the function in question CAN be converted into SQL - EF just isn't realising that it can do so.

我相信这不是这个类似问题的重复：在实体框架查询的选择子句中使用函数，因为在我的场景中，有问题的函数可以转换为SQL - EF只是没有意识到它可以这样做。

The queries are approximately:

查询大约是：

public static IQueryable<Template> WhereIsOwnedByUser(this IQueryable<Template> set, User user)
{
    return set.Where(temp =>
        temp.Requests
            .Where(req => req.WasSent)
            .OrderByDescending(req => req.DueDate)
            .Take(2)
            .SelectMany(req => req.RequestRecipients.Select(reqRecip => reqRecip.Recipient.Id))
            .Contains(user.Id));
}

AND

和

public static IQueryable<Template> WhereIsOwnedByUser(this IQueryable<DataReturn> set, User user)
{
    return set.Where(ret=>
        ret.Entity.Id == user.Entity.Id
        &&
        ret.Request.Template.Requests
            .Where(req => req.WasSent)
            .OrderByDescending(req => req.DueDate)
            .Take(2)
            .SelectMany(req => req.RequestRecipients.Select(reqRecip => reqRecip.Recipient.Id))
            .Contains(user.Id));
}

So a basic BusinessLogic rule for "owns a template" and then a corollary of that for "owns DataReturn if company matches AND owns template"

因此，“拥有模板”的基本BusinessLogic规则，然后是“拥有DataReturn，如果公司匹配AND拥有模板”的必然结果

As you can see, thinking only about the C#, these could easily be refactored as:

正如您所看到的，只考虑C＃，这些可以很容易地重构为：

private static bool UserOwnsTemplate(User user, Template temp)
{
    return temp.Requests
               .Where(req => req.WasSent)
               .OrderByDescending(req => req.DueDate)
               .Take(2)
               .SelectMany(req => req.RequestRecipients.Select(reqRecip => reqRecip.Recipient.Id))
               .Contains(user.Id);
}

public static IQueryable<Template> WhereIsOwnedByUser(this IQueryable<Template> set, User user)
{
    return set.Where(temp => UserOwnsTemplate(user, temp));
}

public static IQueryable<DataReturn> WhereIsOwnedByUser(this IQueryable<DataReturn> set, User user)
{
    return set.Where(
        ret =>
            ret.Entity.Id == user.Entity.Id
            &&
            UserOwnsTemplate(user, ret.Request.Template)
    );
}

Thus reducing the duplication (Yay!)

从而减少重复（耶！）

But then EF will complain that it doesn't know what to do with UserOwnsTemplate, despite the fact that it can handle the logic in SQL perfectly well.

但是EF会抱怨它不知道如何处理UserOwnsTemplate，尽管它可以很好地处理SQL中的逻辑。

AFAICT there is no nice way to solve this. I think my options are:

AFAICT没有很好的方法来解决这个问题。我认为我的选择是：

Turn UserOwnsTemplate into a UDF, a SQL function defined in the database.
- But I can't create a UDF from a C# lamda, I have to define the SQL, which would be more hassle.
- 但我无法从C＃lamda创建UDF，我必须定义SQL，这将更麻烦。
将UserOwnsTemplate转换为UDF，即数据库中定义的SQL函数。但我无法从C＃lamda创建UDF，我必须定义SQL，这将更麻烦。
Assign the Expression<Func<Template,bool>> that UserOwnsTemplate defines as a variable, and then build relevant Expression<Func<DataReturn ,bool>> for the DataReturn version by hand using Expression.AndAlso to glue the two "clauses" together.
- Meta-programming. Ughhh. I've done this before in another project and it was vile to do, and a nightmare to maintain.
- 元编程。 Ughhh。我之前在另一个项目中做过这件事，这样做很卑鄙，而且是一场噩梦。
将UserOwnsTemplate定义的Expression >分配为变量，然后使用Expression.AndAlso手动为DataReturn版本构建相关的Expression >，将两个“子句”粘合在一起。元编程。 Ughhh。我之前在另一个项目中做过这件事，这样做很卑鄙，而且是一场噩梦。
Live with the duplication.
- Likely what will happen unless SO can advise otherwise. ;)
- 可能会发生什么，除非SO可以另有建议。 ;）
与重复一起生活。可能会发生什么，除非SO可以另有建议。 ;）

Can anyone see any other options available?

任何人都可以看到任何其他选项吗？

Can I do anything to force EF into parsing the function into SQL? (the phrase "inling" comes to mind but I don't 100% know what I think I mean by that?)

我可以做任何事情来强制EF将函数解析为SQL吗？（“inling”这个短语浮现在脑海中，但我不知道我认为我的意思是什么？）

Can anyone see a way to convert ret.Request.Template into an IQueryable so that I can just call the other WhereIsOwnedBy extension method on it?

任何人都可以看到一种方法将ret.Request.Template转换为IQueryable，以便我可以调用其他WhereIsOwnedBy扩展方法吗？

Any other suggestions AT ALL?

还有其他任何建议吗？

2 个解决方案

#1

You can keep your syntax and make it work but you will need to call an additional method on the outer IQueryable<>.

您可以保留语法并使其工作，但您需要在外部IQueryable <>上调用另一个方法。

The trick is to manually replace the IQueryable<>.Expression with a copy in which you replace the function call by the corresponding Expression>.

诀窍是手动将IQueryable <>。表达式替换为一个副本，在该副本中用相应的Expression>替换函数调用。

So the idea is to do something like that:

所以我的想法就是这样做：

public static class MyLinqExtensions
{
    public static IQueryable<T> InlineFunctions<T>(this IQueryable<T> queryable)
    {
        var expression = TransformExpression(queryable.Expression);
        return (IQueryable<T>)queryable.Provider.CreateQuery(expression);
    }

    private static Expression TransformExpression(System.Linq.Expressions.Expression expression)
    {
        var visitor = new InlineFunctionsExpressionVisitor();
        return visitor.Visit(expression);
    }

    private class InlineFunctionsExpressionVisitor : System.Linq.Expressions.ExpressionVisitor
    {
        protected override System.Linq.Expressions.Expression VisitMethodCall(System.Linq.Expressions.MethodCallExpression methodCallExpression)
        {   
            if (methodCallExpression.Method.IsStatic
                && methodCallExpression.Method.DeclaringType == typeof(MyDeclaringType)
                && methodCallExpression.Method.Name == "WhereIsOwnedByUser")
            {
                var setArgumentExpression = methodCallExpression.Arguments[0];
                var userArgumentExpression = methodCallExpression.Arguments[1];
                var methodInfo = ... // Get typeof(IQueryable<Template>).MethodInfo
                var whereConditionExpression = ...// Build where condition and use userArgumentExpression
                return Expression.MethodCallExpression(methodInfo, setArgumentExpression, whereConditionExpression);
            }
            return base.VisitMethodCall(methodCallExpression);


            // Some ideas to make this more flexible:
            // 1. Use an attribute to mark the functions that can be inlined [InlinableAttribute]
            // 2. Define an Expression<Func<>> first to be able to get the Expression and substritute the function call with it:
            // Expression<Func<IQueryable<Template>, User, IQueryable<Template>>> _whereIsOwnedByUser = (set, user) => 
            // {
            //  return set.Where(temp => UserOwnsTemplate(user, temp));
            // };
            //
            // public static IQueryable<Template> WhereIsOwnedByUser(this IQueryable<Template> set, User user)
            // {
            //  // You should cache the compiled expression
            //  return _whereIsOwnedByUser.Compile().Invoke(set, user); 
            // }
            //
        }
    }
}

And then you can do this:

然后你可以这样做：

public static IQueryable<DataReturn> WhereIsOwnedByUser(this IQueryable<DataReturn> set, User user)
{
    return set.Where(
        ret =>
            ret.Entity.Id == user.Entity.Id
            &&
            UserOwnsTemplate(user, ret.Request.Template)
    )
    .InlineFunctions();
}

#2

The problem is that your method becomes part of the expression tree and that EF cannot evaluate it. In principle, it is possible to evaluate parts of the expression tree before triggering the query. Have a look at Re-Linq: https://relinq.codeplex.com/ It has a class PartialEvaluatingExpressionTreeVisitor which can evaluate all partial expression trees, i.e. it will find your method, evaluate it, and inject the actual expression tree. This will come at a certain performance cost, but it may not be significant and you will have to gauge clean design versus performance.

问题是您的方法成为表达式树的一部分，EF无法对其进行评估。原则上，可以在触发查询之前评估表达式树的部分。看看Re-Linq：https：//relinq.codeplex.com/它有一个PartialEvaluatingExpressionTreeVisitor类，可以评估所有的部分表达式树，即它会找到你的方法，评估它，并注入实际的表达式树。这将带来一定的性能成本，但可能并不重要，您必须衡量清洁设计与性能。

#1