How can I call a local function inside a LINQ query?

Question

I tried:

 var doctorPractise = from d in db.DoctorsPrivateClinics
                                     where  GetDistance(db, d)<1.0
                                     select d;

But this doesn't work, GetDistance is a local function, I didn't get an exeption but it didn't seem to work, any ideas? Thank you

The GetDistance is:

 private double GetDistance(DBClassesDataContext db, DoctorsPrivateClinic docPra)
    {
        double distance=-1;
        double longitude;
        double latitude;

        var city = from c in db.Cities
                   where c.Code == Convert.ToInt32(Request.QueryString["Area"])
                   select c;


        foreach (var cit in city)//it will be just one row that will be found but I don't know how to express it without foreach
        {
            Calculations.GetLongitudeLatitudeGoogle getLongLat = new Calculations.GetLongitudeLatitudeGoogle();
            getLongLat.GetLongitudeLatitude("", cit.Name, "", out longitude, out latitude);
            distance = CalcualateDistance(Convert.ToDouble(docPra.PrivateClinic.Latitude), Convert.ToDouble(docPra.PrivateClinic.Longtitude), latitude, longitude);
        }
        return distance;
    }

What I want to achieve is to calculate the distance between a specific point on the map (cit.Name) and the location of a private clinic. The longitude and latitude of the specific point on the map is been retrieved from Googlemaps. In my query I specify that this distance must be less than 1 Km

Answer 1

The trouble is that your query is being translated into SQL by the LINQ provider (EF/Linq2Sql?). It's not capable of translation of arbitrary .net methods. The only way round this is to enumerate the entire collection locally, dispensing with the database goodness of indexes etc, and probably bottlenecking on network IO from the DB.

If this isn't a problem:

var doctorPractise = from d in db.DoctorsPrivateClinics.AsEnumerable()
                                 where  GetDistance(db, d)<1.0
                                 select d;

otherwise, consider restating your query in terms that can be translated to SQL. Seeing your GetDistance method would help us to help you here.