CODEWITHSUNDEEP
c#linqentity-framework
Nawaz Dhandala
Nawaz Dhandala

Reputation: 2054

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

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. :)

Upvotes: 3

Views: 6245

Answers (3)

scottt732
scottt732

Reputation: 3937

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);

Upvotes: 0

Stanislav
Stanislav

Reputation: 4569

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

Upvotes: 0

saj
saj

Reputation: 4796

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

Upvotes: 3

Related Questions