Using C# function in Entity Framework Query

Question

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.

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.

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"

As you can see, thinking only about the C#, these could easily be refactored 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 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.

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

• 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.
• 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.
• Live with the duplication.
  • Likely what will happen unless SO can advise otherwise. ;)

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?)

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?

Any other suggestions AT ALL?

Answer 1

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

The trick is to manually replace the IQueryable<>.Expression with a copy in which you replace the function call by the corresponding 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();
}