LINQKit + EF Core: Where clause as method error

Question

I am using LINQKit.Core 1.1.17 and I would like to use my method in Expression Func. My problem is that I am getting error:

The LINQ expression 'DbSet .Where(v => TestService.Distance( x1: __current_X_0, y1: __current_Y_1, x2: v.X, y2: v.Y) < __8__locals1_maxDistance_2)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Here is my code.

Expression:

Expression<Func<Test, bool>> exp = v => Distance(current.X, current.Y, v.X, v.Y) < maxDistance;

Distance method:

    private double Distance(int x1, int y1, int x2, int y2)
    {
        var x = Math.Abs(x1 - x2);
        var y = Math.Abs(y1 - y2);

        return Math.Sqrt(x ^ 2 + y ^ 2);
    }

Is it possible to accomplish that using LINQKit? Are there better ways to query database in that way?

You can't pass methods down to the database. Everything that is going to be running in the database has to be translatable to SQL. You will have do run that method client-side. Try `DbSet.AsEnumerable().Where(...` — Lasse V. Karlsen, Apr 10 '20 at 20:09
You could always `Expression> Distance = (x1, y1, x2, y2) => Math.Sqrt(Math.Pow(x1 - x2, 2) + Math.Pow(y1 - y2, 2))`. By the way, in C#, `^` is not the exponentiation operator. — Aluan Haddad, Jul 20 '20 at 18:58

score 0 · Accepted Answer · answered Mar 16 '21 at 07:29

You can define this method as marked by ExpandableAttribute

public static class HelperFunctions
{
    [Expandable(nameof(DistanceImpl))]
    public static double Distance(int x1, int y1, int x2, int y2)
    {
        var x = Math.Abs(x1 - x2);
        var y = Math.Abs(y1 - y2);

        return Math.Sqrt(x ^ 2 + y ^ 2);
    }

    private static Expression<Func<int, int, int, int, double>> DistanceImpl()
    {
        rerutn (x1, y1, x2, y2) =>
            Math.Sqrt((x1 - x2) * (x1 - x2) + (y1 - y2) * (y1 - y2));
    }
}

Then you can use this function in filters:

var query = query
   .AsExpandable()
   .Where(q => HelperFunctions.Distance(q.x1, q.y1, q.x2, q.y2) < maxDistance);

LINQKit will inject lambda body defined in DistanceImpl and make EF LINQ translator happy.

LINQKit + EF Core: Where clause as method error

1 Answers1