Breaking LINQ to Entities into multiple parts

Question

I had the following code

query = query.Where(device => GetDeviceDistanceFromGeocode(geocode, device) <= distanceMetres);

private static double GetDeviceDistanceFromGeocode(Geocode geocode, Device device)
{
    return Math.Pow(
        Math.Pow(device.Address.Geocode.Easting - geocode.Easting, 2) +
        Math.Pow(device.Address.Geocode.Northing - geocode.Northing, 2)
        , 0.5);
}

However it throws an exception because linq cannot recognise my function forcing me into doing the whole query expression in one go.

Exception[LINQ to Entities does not recognize the method 
'Double DistanceOfDeviceFromGeocode(Geocode, Device)' method, 
and this method cannot be translated into a store expression.]

Is it possible to break a query expression out into multiple parts like i'm trying to do here? It's not very readable when the query is so big.

EDIT:

Here's the full query as asked for in the comments.

return query.Where(device => 
            Math.Pow(
                Math.Pow(device.Address.Geocode.Easting - geocode.Easting, 2) + 
                Math.Pow(device.Address.Geocode.Northing - geocode.Northing, 2)
                , 0.5) 
                <= distanceMetres);

Basically I don't think this is very readable so would like to break it down into multiple parts but from the links supplied it doesn't seem possible.

In c++ I could have broken some of it out into a macro but unfortunately this isn't a feature of c#.

Following suggestions I've updated the code to this which works quite nicely and improves the readability of the code a lot!

    return query.Where( DevicesWithinDistanceFromGeocode(distanceMetres, geocode) );
}

public Expression<Func<Device, bool>> DevicesWithinDistanceFromGeocode(double distance, Geocode geocode)
{
    return device => (  SqlFunctions.SquareRoot(
                            SqlFunctions.Square((double)(device.Address.Geocode.Easting - geocode.Easting)) +
                            SqlFunctions.Square((double)(device.Address.Geocode.Northing - geocode.Northing))
                        ) <= distance );
}

Can you clarify what you mean by `forcing me into doing the whole query expression in one go`? I've got an idea as to how you might be able to break it down but I'm not familiar with L2E so I'm not sure if it will work; if you can show your "big query" it might help. — Rawling, Oct 10 '12 at 15:19
OK, if that's your full query then my suggestion isn't much good :D I assumed you'd have several sections like that combined into one query. — Rawling, Oct 10 '12 at 15:27

score 2 · Accepted Answer · edited May 23 '17 at 12:18

Technically there are ways to do it, however since Linq can't directly translate your custom code to SQL they require returning ALL results to Linq and then evaluating the custom methods.

Since you're using this in a Where clause I'm assuming that you expect to retrieve a relatively small number of records. In that case I would recommend coding your condition in SQL, either in a stored procedure or scalar-valued function, then using that sproc/function in your Linq-to-Entities query.

score 1 · Answer 2 · answered Oct 10 '12 at 15:43

I can't quite see what you're getting at; if you're only using this code in one place, and it's the only clause in your query, then moving the calculation into a separate method doesn't really change much.

If, instead, you are looking to re-use your distance calculation code in several places and don't want to repeat it, or if you have a number of checks you want to call in one query and don't want that query getting too unwieldy, you could try e.g.

public static IQueryable<Device> WhereCloseToGeocode(
    this IQueryable<Device> source,
    Geocode geocode,
    double distanceMetres)
{
    return source.Where(device => Math.Pow(
        Math.Pow(device.Address.Geocode.Easting - geocode.Easting, 2) +
        Math.Pow(device.Address.Geocode.Northing - geocode.Northing, 2),
        0.5) <= distanceMetres);  
}

public static IQueryable<Device> OtherCondition(
    this IQueryable<Device> source, ...)
{
   ...
}


...
devices = devices
    .WhereCloseToGeocode(geocode1, 3)
    .WhereCloseToGeocode(geocode2, 7)
    .OtherCondition(...);

It's not quite as good as making the distance calculation its own method - for example, you'd need one method for "closer than" and a separate method for "further than" - but it should work in L2E as it's essentially the same as chaining your original Where clause, which you say works.

score 0 · Answer 3 · answered Oct 10 '12 at 15:04

0

Your problem is, that you're using the device in the method. Linq to Entities has to translate your Linqquery to an SQLQuery.

You have to select all device from the database first to succeed with this query.

Look up the static class SqlMethods to find other examples for this problem :)

answered Oct 10 '12 at 15:04

Jan P.

3,261
19
26

This doesn't answer my question on how to separate the query into multiple parts, got it working as above though – Neil Oct 10 '12 at 20:22

Piotr Justyna · Answer 4 · 2012-10-10T15:15:44.537

0

This can be quite expensive (depending on the size of your table), but will work:

query = query.ToList().Where(device => GetDeviceDistanceFromGeocode(geocode, device) <= distanceMetres);

In this approach you first evaluate the query and then run your method against each and every record on the client side.

edited Oct 10 '12 at 15:15

answered Oct 10 '12 at 15:09

Piotr Justyna

4,888
3
25
40

I'd rather not go down that route because I have other searches that I build into the query before and after. I guess without a preprocessor macro this won't be possible. – Neil Oct 10 '12 at 15:13
Have you tried to change your GetDevices... method to return a lambda instead? and then use it in your query? Please take a look at this answer: http://stackoverflow.com/a/2043169/224612 – Piotr Justyna Oct 10 '12 at 15:14
-1 because as you point out: "... run your method against each and every record on the client side...". We can't assume his query won't return 10,000 records (or 100,000 or 1,000,000...) – Adriano Repetti Oct 10 '12 at 15:39
This is why I wrote that it can be quite expensive depending on the size of the table. Don't understand your downvote really. – Piotr Justyna Oct 10 '12 at 15:41
If you use `AsEnumerable` rather than `ToList`, would that reduce the load at all? – Rawling Oct 10 '12 at 15:44
Well, 'cause I think it can't be a general solution (even if it can be a solution in some special cases) because of this. It'll transfer ALL data from server to client to do that processing (so you'll use CPU time both on server and client side and bandwidth). It's not viable if we don't know more about the problem (of course it can be a good solution **in his case** and then he'll upvote and accept the answer). – Adriano Repetti Oct 10 '12 at 15:47
That's a bit harsh I think. I warned the OP that it can be expensive specifically because I don't know the size of his table. It's not a general solution and I described why in my answer. Getting a downvote for that seems unfair. – Piotr Justyna Oct 10 '12 at 15:52

Breaking LINQ to Entities into multiple parts

4 Answers4