Support defining user methods that get translated to LINQ in queryies

[SoftCircuits]

Problem Description

Virtually everyone who is new to Entity Framework has at some time got an error because they tried to call a C# method that could not be converted to SQL. It is common to want to do this. And support for some functions has been added via EF.Functions, etc.

But many people would like to be able to add custom methods specific to their own requirements. This can be done using IMethodCallTranslator or DbFunctionBuilder.HasTranslation().

But writing even slightly complex expressions with either of these two methods is challenging and overly difficult. It would be very powerful to be able to more easily create custom methods and specify the SQL that they generate.

Ideal Solution

The following code works with LINQKit. It defines an extension method that can be used in a query. The method uses the Expandable attribute to specify the method that returns the corresponding expression to be converted to SQL.

public static class QueryHelper
{
    [Expandable(nameof(GetRailcarQuantity))]
    public static double GetCurrentQuantity(this Railcar railcar, ApplicationDbContext dbContext)
    {
        throw new InvalidOperationException("This method cannot be executed directly.");
    }

    public static Expression<Func<Railcar, ApplicationDbContext, double>> GetRailcarQuantity()
    {
        return (railcar, dbContext) => railcar.InboundQuantity
            - dbContext.Transfers
                .Where(t => t.FromId == railcar.Id && t.FromType == TransferType.Railcar)
                .Sum(t => t.Quantity)
            + dbContext.Transfers
                .Where(t => t.ToId == railcar.Id && t.ToType == TransferType.Railcar)
                .Sum(t => t.Quantity);
    }
}

Using LINQKit, you can then use AsExpandable() and this method will be supported in a query.

var r = DbContext.Railcars
    .AsExpandable()
    .Where(r => r.FacilityId == UserContext.FacilityId)
    .OrderBy(r => r.Arrival)
    .Select(r => new
    {
        r.RailcarNumber,
        Quantity = r.GetCurrentQuantity(dbContext)
    })
    .ToList();

This works great, but it creates yet another dependency and a reliance on the authors to maintain the library. Worse, if you look into this package, you'll see it has many different versions for different versions of .NET. This indicates the code has a heavy reliance on .NET internals and is likely to break as I upgrade to future versions of .NET.

What would be really great is if EFCore supported this directly. It would be a powerful tool that would allow for much cleaner queries for commonly used business logic.

[roji]

Note that EF already has support for user-defined function mapping, so there's no need for e.g. an [Expandable] attribute/API (we already have modelBuilder.HasDbFunction).

I think the request here basically to allow defining the function as a regular pre-translation LINQ query, rather than as a SQL tree. Having the user provide a a SQL expression tree directly is indeed problematic once you start going beyond the trivial - not possible to construct SelectExpression, need to take care of type mapping and inference, etc. etc. If we allowed a regular pre-translation expression tree, the user could use C# to express it, and we'd simply integrate it into the tree during pretranslation.

At least conceptually, this would be pretty similar to query filters - a user-specified pre-translated LINQ tree taken from the model and integrated into the query in pretranslation.

[SoftCircuits]

@roji Yes, I think that's the issue. And yes, we have this ability now with Where(), and I assume the same could be done with Select(). Just not within a Select() expression.

[roji]

Yep, for any top-level queryable operators, you can easily write a replacement operator which injects whatever you want into the query tree. But anything within an expression lambda gets quoted rather than executed, so that has to work differently (note that using e.g. a Where replacement inside a lambda - such as a Select - also won't work in a simple way).

[roji]

To clarify... EF currently allows mapping arbitrary .NET methods to SQL functions which already exist in the database; the user is responsible for defining those SQL functions (e.g. via custom SQL in migrations), and then these can be invoked seemlessly in LINQ queries.

Here, I think we should track allowing users to specify that an arbitrary .NET method should translate to an arbitrary LINQ construct; this would allow reusing common LINQ subtree fragments across queries rather than forcing users to repeat them. This corresponds to mistake done by many new users who attempt to factor out parts of a LINQ query to a method, and then get an EF "can't translate" error for that method invocation.

Note that similarity with query filters, which we already support; filters attach an arbitrary LINQ subtree to any query on a specific entity type, whereas this feature would allow mapping a specific method to an arbitrary LINQ subtree.