Database functions in Entity Framework could not return results

I have implemented the same function "distancebetween" as in Nerddinner. I created an airport repository and have these methods:

    public IQueryable<AllAirports> ReturnAllAirportWithIn50milesOfAPoint(double lat, double lon)
    {
        var airports = from d in im.AllAirports
               where DistanceBetween(lat, lon, (double)d.Lat, (double)d.Lon) < 1000.00
               select d;
        return airports;
    }

    [EdmFunction("AirTravelModel.Store", "DistanceBetween")]
    public static double DistanceBetween(double lat1, double long1, double lat2, double long2)
    {
        throw new NotImplementedException("Only call through LINQ expression");
    }

When I tested it, it shows:

base {"The specified method 'Double DistanceBetween(Double, Double, Double, Double)' on the type 'AirTravelMVC3.Models.Repository.AirportRepository' cannot be translated into a LINQ to Entities store expression because no overload matches the passed arguments."} System.SystemException {System.NotSupportedException}

Do you have any ideas on why this happen? The only different between my work and nerddinner is that I used a POCO plugin in entity framework.

The SQL UDF is as follows, it works very well in the database:

CREATE FUNCTION [dbo].[DistanceBetween](@Lat1 as real,
@Long1 as real, @Lat2 as real, @Long2 as real)
RETURNS real
AS
BEGIN

DECLARE @dLat1InRad as float(53);
SET @dLat1InRad = @Lat1 * (PI()/180.0);
DECLARE @dLong1InRad as float(53);
SET @dLong1InRad = @Long1 * (开发者_如何学CPI()/180.0);
DECLARE @dLat2InRad as float(53);
SET @dLat2InRad = @Lat2 * (PI()/180.0);
DECLARE @dLong2InRad as float(53);
SET @dLong2InRad = @Long2 * (PI()/180.0);

DECLARE @dLongitude as float(53);
SET @dLongitude = @dLong2InRad - @dLong1InRad;
DECLARE @dLatitude as float(53);
SET @dLatitude = @dLat2InRad - @dLat1InRad;
/* Intermediate result a. */
DECLARE @a as float(53);
SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad)
* COS (@dLat2InRad)
* SQUARE(SIN (@dLongitude / 2.0));
/* Intermediate result c (great circle distance in Radians). */
DECLARE @c as real;
SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a));
DECLARE @kEarthRadius as real;
/* SET kEarthRadius = 3956.0 miles */
SET @kEarthRadius = 6376.5;        /* kms */

DECLARE @dDistance as real;
SET @dDistance = @kEarthRadius * @c;
return (@dDistance);
END
 

In order to do this using POCOs with the existing NerdDinner source I had to add this to the DinnerRepository class:

public IQueryable<Dinner> FindByLocation(float latitude, float longitude)
{
    List<Dinner> resultList = new List<Dinner>();

    var results = db.Database.SqlQuery<Dinner>("SELECT * FROM Dinners WHERE EventDate >= {0} AND dbo.DistanceBetween({1}, {2}, Latitude, Longitude) < 1000", DateTime.Now, latitude, longitude);
    foreach (Dinner result in results)
    {
        resultList.Add(db.Dinners.Where(d => d.DinnerID == result.DinnerID).FirstOrDefault());
    }

    return resultList.AsQueryable<Dinner>();
}

Assuming DistanceBetween is implemented in c# The issue (as hinted at by @p.campbell) is that the query generator doesn't know how to calculate DistanceBetween

To get your code to work as is, you might need to do something like

public IQueryable<AllAirports> ReturnAllAirportWithIn50milesOfAPoint(double lat, double lon)
{
    var airports = from d in im.AllAirports.ToList()
           where DistanceBetween(lat, lon, (double)d.Lat, (double)d.Lon) < 1000.00
           select d;
    return airports;
}

the ToList() will force the AllAirports to evaluate to a list, then it can be evaluated in memory, using your c# function. Obviously this won't scale to a huge number of airports. If that was an issue, you might want to do a rough "box" query where you just do a cheap within-a-square expression to return a small number of airports, ToList that, and then call your distancebetween to refine the results.

I got this same exception after I made several changes to the DB schema and "updated the model from database".

After comparing my EDMX XML with the original from Nerd Dinner, I saw that mine had changed all of the types for the DistanceBetween function to "real", where Nerd Dinner had "float". Changing them back to float resolved the issue.