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.