Linq OrderBy calculation of properties (with join)

Question

I have two database tables that map to models in my MVC app:

public class BuildingLocation
{
    public int id { get; set; }
    public double Lat { get; set; }
    public double Lng { get; set; }
}

public class Building
{
    public int id { get; set; }
    public string title { get; set; }
    //OTHER STUFF
}

Using Linq / Entities, I'm trying to get a list of Buildings, ordered by distance from a given point on a map.

DefaultConnection db = new DefaultConnection();    

 public IEnumerable<dynamic> GetBuildings(double north, double south, double east, double west)
    {
        double centreX = (east - west) / 2;
        double centreY = (north - south) / 2;
        var query = from b in db.Building
                    join l in db.BuildingLocation on
                    b.id equals l.id
                    select new {b.id, b.title, l.Lat, l.Lng,
                            dist = Math.Sqrt(((centreX - l.Lat) * (centreX - l.Lat)) + ((centreY - l.Lng) * (centreY - l.Lng)))
                    };

        query = query.Where(l => l.Lat > west);
        query = query.Where(l => l.Lat < east);
        query = query.Where(l => l.Lng > south);
        query = query.Where(l => l.Lng < north);
        query = query.OrderBy(c => c.dist);
        return query.AsEnumerable();
       }

So, obviously this doesn't work at all. I've never used Linq before. How can I set the OrderBy based on a calculation?

It's not clear that it won't work, to be honest - what happened when you tried it? (It depends on whether Math.Sqrt and all of the arithmetic is supported.) — Jon Skeet, Aug 29 '12 at 16:30
You could create a scalar distance function in the database and call that as part of your query. Since I've only done that in Linq2SQL myself, I can't give a full answer, but http://stackoverflow.com/questions/3500509/calling-user-defined-functions-in-entity-framework-4 might fill in the gaps. — Jon Hanna, Aug 29 '12 at 16:39

Kundan Singh Chouhan · Answer 1 · 2012-08-29T17:02:19.723

Try this :

public IEnumerable<dynamic> GetBuildings(double north, double south, double east, double west)
{
    double centreX = (east - west) / 2;
    double centreY = (north - south) / 2;
    var query = db.Building.Join(db.BuildingLocation.Where(l=> 
                             l.Lat > west &&  l.Lat < east 
                             && l.Lng > south && l.Lng < north), 
                             b => b.id , l => l.id, 
                             (b,l) => new {
                                            ID = b.id,
                                            Title = b.title,
                                            Lat = l.lat,
                                            Lng = l.Lng,
                                            dist =  Math.Sqrt(((centreX - l.Lat) * (centreX - l.Lat)) + ((centreY - l.Lng) * (centreY - l.Lng)))
                                          }).OrderBy(Q=>Q.dist);

    return query;
}

Above query is written in lambda expression. If you want this in query expression use below code :

var query =  from result in (from b in db.Building
                join l in db.BuildingLocation on
                b.id equals l.id
                where l.Lat > west &&  l.Lat < east && l.Lng > south && l.Lng < north
                select new {b.id, b.title, l.Lat, l.Lng,
                        dist = Math.Sqrt(((centreX - l.Lat) * (centreX - l.Lat)) + ((centreY - l.Lng) * (centreY - l.Lng))) })
                order by result.dist select result;

This will fix your issue.

score 1 · Answer 2 · answered Aug 29 '12 at 17:49

If you use Entity framework you probably got the following exception message:

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

So i would solve this in two steps, the inner query should only get data from tables, and in the outer (now in-memory query) will do the distance calculation and ordering:

double centreX = (east - west) / 2;
double centreY = (north - south) / 2;

var query = (from result in
                ((from b in db.Building
                    join l in db.BuildingLocation on b.id equals l.id
                    where l.Lat > west && l.Lat < east && l.Lng > south && l.Lng < north
                    select new { b.id, b.title, l.Lat, l.Lng }).AsEnumerable()
                )
            select new
            {
                id = result.id,
                title = result.title,
                Lat = result.Lat,
                Lng = result.Lng,
                dist = Math.Sqrt(((centreX - l.Lat) * (centreX - l.Lat)) + ((centreY - l.Lng) * (centreY - l.Lng)))
            }).OrderBy(e => e.dist);

Thanks. I didn't fully appreciate the difference between Linq translating to a store expression, and what happens in- memory. — user888734, Aug 29 '12 at 20:25
LINQ works differently if you use local (in-memory) query or remote (EF, LINQ2SQL) query. In the first case only ordinary .net code is involved, but int the second case an expression tree is generated first and interpreted later. So there are some linq queries that will work on local, but unable to convert to expression tree, like your query in the original question. — Laszlo Boke, Aug 30 '12 at 06:49

score 0 · Answer 3 · answered Aug 29 '12 at 16:43

Off the top of my hat, and just for the buildings, I'd do something like this:

public IEnumerable<Building> GetBuildings(double north, double south, double east, double west)
{
    double centreX = (east - west) / 2;
    double centreY = (north - south) / 2;

    db.BuildingLocation.Where(l => l.Lat > west && l.Lat < east && l.Lng > south && l.Lng < north)
        .Join(
            db.Building, 
            b => b.id, 
            l => l.id, 
            (l, b) =>  new {Building = b, l.Lat, l.Lng })
        .AsEnumerable()
        .OrderBy(l => Math.Sqrt(((centreX - l.Lat) * (centreX - l.Lat)) + ((centreY - l.Lng) * (centreY - l.Lng))))
        .Select(l => l.Building)
}

I'm assuming it doesn't work because the distance calculation can't be translated to SQL, so I've inserted the AsEnumerable call before the OrderBy -- that means the filtering happens on the database side, but the ordering happens in-memory with Linq to Objects.

Linq OrderBy calculation of properties (with join)

3 Answers3