Using a C # Function in an Entity Framework Request

Question

In my C # code, I have 2 WHERE queries, both of which I can call IQueryable, and all this compiled before SQL, and both of them have a lot of common logic.

I believe this is not a duplication of this similar question: Using a function in a Select Entity Framework Query clause , because in my script the function in question can be converted to SQL-EF, it just does not understand that it can do this.

Requests 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, the basic BusinessLogic rule for "owns the template", and then the consequence for "belongs to DataReturn if the company matches the AND own template"

As you can see, thinking only about C #, they can be easily reorganized as:

 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 duplication (Yay!)

But , then EF will complain that he does not know what to do with UserOwnsTemplate , despite the fact that he copes with the logic in SQL.

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

• Turn UserOwnsTemplate to UDF, the SQL function defined in the database.
  • But I cannot create UDF from C # lamda, I have to define SQL, which will be more complex.
• Assign Expression<Func<Template,bool>> , which UserOwnsTemplate defines as a variable, and then create the corresponding Expression<Func<DataReturn ,bool>> for the DataReturn version manually, using Expression.AndAlso to glue the two sentences together.
  • Meta programming. Ughhh. I did this earlier in another project, and it was nasty, and a nightmare to maintain.
• Live with duplication.
  • It is likely to happen unless SO reports otherwise .;)

Can anyone see the other available options?

Can I do anything to get EF to parse a function in SQL? (the phrase "inling" comes to mind, but I don’t know what I think what I mean?)

Can someone see a way to convert ret.Request.Template to IQueryable so that I can just call another WhereIsOwnedBy extension method?

Any other suggestions in EVERYONE?

Answer 1

You can save your syntax and make it work, but you will need to call an additional method for the external IQueryable <>.

The trick is to manually replace the IQueryable <> expression. a copy in which you replace the function call with the appropriate> expression.

So, the idea is to do something like this:

 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 it:

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

Answer 2

The problem is that your method becomes part of the expression tree and that EF cannot evaluate it. Basically, you can evaluate parts of the expression tree before you run the query. Take a look at Re-Linq: https://relinq.codeplex.com/ It has a PartialEvaluatingExpressionTreeVisitor class that can evaluate all partial expression trees, i.e. find your method, evaluate it and enter the actual expression tree. This will happen with a certain performance, but it may not be very important, and you will have to evaluate the clean design and performance.