Filtering results with custom Expression in EF Core

Question

Based on other questions and answers in SO, I tried using a custom expression to filter some results from my database but without success. It's about serching results into specific location by latitude, longitude and radius. When i run the query it throws an exception like the example bellow:

System.InvalidOperationException: The LINQ expression 'DbSet<Post>
.Where(p => ExpressionUtils.IsInRadius(
    latA: p.Inner.Lat, 
    lngA: p.Inner.Lng, 
    latB: lat2, 
    lngB: lng2, 
    radius: radius))' 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().
...

The query looks as follow:

...

this.dbContext.Posts.Where(ExpressionUtils.CheckInRadius(x => x.Location.Lat, x => x.Location.Lng, model))
...

And the best type of implementation that I've found and changed for my case looks like this:

public static partial class ExpressionUtils
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    public static Expression<Func<Post, bool>> CheckInRadius(Expression<Func<Post, decimal>> latSrcExp, Expression<Func<Post, decimal>> lngSrcExp, FilterPostsDTO model)
    {
        var entity = Expression.Parameter(typeof(Post));
        var latExp = latSrcExp.Body.ReplaceParameter(latSrcExp.Parameters[0], entity);
        var lngExp = lngSrcExp.Body.ReplaceParameter(lngSrcExp.Parameters[0], entity);

        Expression<Func<decimal, decimal, decimal, decimal, int, bool>> baseExpr
            = (latA, lngA, latB, lngB, radius) => IsInRadius(latA, lngA, latB, lngB, radius);

        var lat = baseExpr.Parameters[0];
        var lng = baseExpr.Parameters[1];
        var expr = baseExpr.Body
            .ReplaceParameter(lat, latExp)
            .ReplaceParameter(lng, lngExp);

        return Expression.Lambda<Func<Post, bool>>(expr, entity);
    }

    private static bool IsInRadius(decimal latA, decimal lngA, decimal latB, decimal lngB, int radius)
    {
        if (latB == 0 && lngB == 0 && radius == 0)
        {
            return true;
        }

        static double toRadians(decimal x) => (double)x * Math.PI / 180.0;

        var lat1 = toRadians(latA);
        var lon1 = toRadians(lngA);
        var lat2 = toRadians(latB);
        var lon2 = toRadians(lngB);
        var d_lat = lat2 - lat1;
        var d_lon = lon2 - lon1;
        var h = Math.Pow(Math.Sin(d_lat / 2), 2) + Math.Cos(lat1) * Math.Cos(lat2) * Math.Pow(Math.Sin(d_lon / 2), 2);
        var distance = Math.Abs(Math.Asin(Math.Sqrt(h)) * 6371 * 2);

        return distance <= radius;
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;

        public Expression Target;

        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

So obviously it doesn't work. Am I missing something or am I doing something wrong? Can somewone help?

Any help is appreciated.

Answer 1

If you are just trying to use the custom function "IsInRadius(....)" as part of WHERE clause, then below example works good.

public class Post
{
    public decimal latA;
    public decimal lngA;
    public decimal latB;
    public decimal lngB;
    public int radius;
}

I just created the list, instead of retreiving from database and then called the custom function in the filter, very similar to what you described.

List<Post> posts = new List<Post>()
{
    new Post() { latA = 10, latB = 12, lngA = 3, lngB = 4, radius = 4 },
    new Post() { latA = 13, latB = 11, lngA = 1, lngB = 12, radius = 1 },
    new Post() { latA = 15, latB = 4, lngA = 7, lngB = 8, radius = 6 },
    new Post() { latA = 17, latB = 2, lngA = 2, lngB = 9, radius = 2 },
};

List<Post> y0 = posts.ToList()
            .Where(x => (ExpressionUtils.IsInRadius(x.latA, x.lngA, 0, 0, x.radius)) == false)
            .ToList()
            ;

Answer 2

Well you are trying to use Math.Pow, Math.Sin, Math.Cos etc. in the expression... I don't think most (sic. any?) database systems support these functions in SQL...

You can either .ToList() before applying the filter (although that will pull ALL results back to memory, and then filter them, so not ideal for performance), or you can try to use like a CLR SProc if using MSSQL, or the database-native spatial queries if your database system supports them