EF Expression Get Locations within Distance

Question

I'm trying to query my database to find all events within a certain location. I would like to be able to query this via the DB so I don't have to pull all of the events so I tried to convert some code into an Expression. The Original code was found here:

public static Expression<Func<Location, bool>> GetWithinDistanceExpression(this Location current, double withinDistance, DistanceUnits units)
{
    //in the EQ converts something to meters
    double toMeters = 6376500;
    double toRadiants = 0.0174532925199433;
    double currentLat = current.Latitude * toRadiants;
    double currentLong = current.Longitude * toRadiants;
   
    return loc => 

        (2 * Math.Atan2(
                Math.Sqrt(
                    //TODO: Merge Expressions instead of copy pasta.
                    Math.Pow(Math.Sin((loc.Latitude - currentLat) / 2), 2) + Math.Cos(currentLat) * Math.Cos(loc.Latitude * toRadiants)
                        * Math.Pow(Math.Sin((loc.Longitude - currentLong) / 2), 2)
                ),
                Math.Sqrt(1 -
                    //TODO: Merge Expressions instead of copy pasta.
                    Math.Pow(Math.Sin((loc.Latitude - currentLat) / 2), 2) + Math.Cos(currentLat)
                        * Math.Cos(loc.Latitude * toRadiants) * Math.Pow(Math.Sin((loc.Longitude - currentLong) / 2), 2)
                )
            )
        )    
            * toMeters
            < withinDistance;
}

But this does not return anything when I query the Db for thing that are within location. My guess is this has to do with the precision during casting.

How can I get locations within a specific distance of 2 coordinates?

I get the feeling this would be better implemented as a user defined function (to calc the distance), or maybe even a TVF (to accept a parameter for the distance and return rows), in the db and then call it from EF, rather than hoping EF will translate your c# accurately to SQL. Have you debug dumped the generated SQL and checked it? Is it even doing the translate or is it dragging all the rows to the client and evaluating there? — Caius Jard, Jun 28 '20 at 04:57

score 0 · Accepted Answer · answered Jul 26 '20 at 18:44

    public static IQueryable<Location> GetLocationsWithinMeter(this DbSet<Location> locations, double lat, double longitude, double withinMeters)
    {
        //Use Interpolated to create parameterized Query
        return locations.FromSqlInterpolated(
            @$"
        SELECT * FROM(
            SELECT l.*, (
              6371392.896 * acos (
              cos ( radians({lat}) )
              * cos( radians( l.[Latitude] ) )
              * cos( radians( l.[Longitude] ) - radians({longitude}) )
              + sin ( radians({lat}) )
              * sin( radians( [Latitude] ) )
            )
        ) AS distance
        FROM Locations l) locInfo
        WHERE distance < {withinMeters}"
        );
    }

EF Expression Get Locations within Distance

1 Answers1