LINQ to Entities does not recognize the method and this method cannot be translated into a store expression

Question

Here's the Linq to Entities Statement which I've written.

public static List<Model.User> GetNearestUsers(int userid,int count,ref Model.HangoutDBEntities context)
    {
        Model.Location myLocation=GetCurrentLocation(userid,ref context);

        return context.Locations.Where(o => EntityFunctions.DiffMinutes(DateTime.Now, o.DateTimeStamp) <= 30).OrderBy(o => Core.Location.Distance.CalculateDistance(myLocation.Latitude, myLocation.Longitude, o.Latitude, o.Longitude)).Select(o => o.User).ToList();
    }

And here's the CalculateDistance Method

public static double CalculateDistance(decimal lat1,decimal lon1,decimal lat2,decimal lon2)
    {
        try
        {
            var R = 6371; // Radius of the earth in km
            var dLat = DegreeToRadian((double)(lat2 - lat1));  // Javascript functions in radians
            var dLon = DegreeToRadian((double)(lon2 - lon1));
            var a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
                    Math.Cos(DegreeToRadian((double)lat1)) * Math.Cos(DegreeToRadian((double)lat2)) *
                    Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
            var c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
            var d = R * c; // Distance in km

            return (double)d;
        }
        catch
        {
            return 0.0;
        }
    }

    public static double DegreeToRadian(double angle)
    {
        return Math.PI * angle / 180.0;
    }

Please let me know if there are any workarounds for this. I get an Exception

LINQ to Entities does not recognize the method 'Double CalculateDistance(System.Decimal, System.Decimal, System.Decimal, System.Decimal)' method, and this method cannot be translated into a store expression.

I know custom functions don't work with Linq 2 Entities but I have no idea on the workarounds for this. Can you please help me out. :)

If you are using sql-server, I would strongly suggest looking at using spatial data types instead of doing the calculation in memory. The problem is that your calculation can't be converted to SQL.http://msdn.microsoft.com/en-us/library/bb933876(v=sql.105).aspx — StuartLC, Aug 31 '12 at 14:34

score 3 · Answer 1 · answered Aug 31 '12 at 14:37

load the data into a list and then perform the Caclulation on the List

    var origList = context.Locations.Where(o => EntityFunctions.DiffMinutes(DateTime.Now, o.DateTimeStamp) <= 30).ToList()

 var orderedList = origList.OrderBy(o => Core.Location.Distance.CalculateDistance(myLocation.Latitude, myLocation.Longitude, o.Latitude, o.Longitude)).Select(o => o.User).ToList();

Or;

context.Locations
    .Where(o => EntityFunctions.DiffMinutes(DateTime.Now, o.DateTimeStamp) <= 30).ToList()
        .OrderBy(o => Core.Location.Distance.CalculateDistance(myLocation.Latitude, myLocation.Longitude, o.Latitude, o.Longitude))
            .Select(o => o.User).ToList();

The thing to note is the ToList after the where clause.

Upon calling the ToList(), the data is loaded into memory and the ordering becomes a task for Linq To Objects

score 0 · Answer 2 · edited May 23 '17 at 12:08

0

Take a look at https://stackoverflow.com/a/5971677/292787

you can define a custom method ..., and tell Entity Framework how to translate that method to SQL

edited May 23 '17 at 12:08

Community

1
1

answered Jan 20 '14 at 14:48

Stanislav

4,389
2
33
35

score 0 · Answer 3 · answered Jun 13 '16 at 17:46

I recently had to solve this problem. The trick is to use SqlFunctions from System.Data.Entity.SqlServer instead of System.Math for most of the statements (except Math.Pow).

The alternate LINQ style (from/where/let/orderby etc.) is useful because the let keyword allows you decompose the problem and keep things readable.

var closestLocation = await (
    from q in qryable
    where q.LAT.HasValue && q.LONG.HasValue
    // Convert derees to radians (pi / 180 = 0.01745...)
    let dbLatitudeRadians = q.LAT.Value * 0.0174532925199433
    let dbLongitudeRadians = q.LONG.Value * 0.0174532925199433
    let requestLatitudeRadians = requestLatitude * 0.0174532925199433
    let requestLongitudeRadians = requestLongitude * 0.0174532925199433
    let deltaLatitudeRadians = requestLatitudeRadians - dbLatitudeRadians
    let deltaLongitudeRadians = requestLongitudeRadians - dbLongitudeRadians
    let sinHalfLatitudeDistance = SqlFunctions.Sin(deltaLatitudeRadians / 2) ?? 0.0
    let sinHalfLongitudeDistance = SqlFunctions.Sin(deltaLongitudeRadians / 2) ?? 0.0
    let cosThisLatitude = SqlFunctions.Cos(dbLatitudeRadians) ?? 0.0
    let cosThatLatitude = SqlFunctions.Cos(requestLatitudeRadians) ?? 0.0
    let expr = Math.Pow(sinHalfLatitudeDistance, 2) + cosThisLatitude * cosThatLatitude * Math.Pow(sinHalfLongitudeDistance, 2)
    let sqrtExpr = Math.Pow(expr, 0.5)
    let sqrt1MinusExpr = Math.Pow(1 - expr, 0.5)
    let distanceInMeters = 6376500 * 2 * SqlFunctions.Atan2(sqrtExpr, sqrt1MinusExpr)
    orderby distanceInMeters
    select q
).FirstOrDefaultAsync(cancellationToken).ConfigureAwait(false);

LINQ to Entities does not recognize the method and this method cannot be translated into a store expression

3 Answers3