Tip 31 – How to compose L2O and L2E queries

Imagine you want to write a query like this:

var possibleBuyers=
from p in ctx.People
where p.Address.City == “Sammamish” && InMarketForAHouse(p)
select p;

Now theoretically this is possible so long as there is a SQL translation for InMarketForAHouse. 

In EF 4.0 you do this by creating a CLR stub for the required Model or Database function.

Imagine however there is no SQL translation.

Perhaps the function needs to use all sort of things that aren’t in the database.

Now you have to ‘partition’ your query. I.e. separate the query into a base LINQ to Entities query and a dependent LINQ to Objects query.

You might try something like this:

var partialFilter = from p in ctx.People
where p.Address.City == “Sammamish”
select p;

var possibleBuyers = from p in partiallyFilter
where InMarketForAHouse(p);
select p;

But this has virtually no effect on the behavior of the code. The IQueryable (ctx.People) will still be asked to translate InMarketForAHouse(..).

You need a call to AsEnumerable(), which effectively isolates the two sections of the query:

var possibleBuyers = from p in partiallyFilter.AsEnumerable()
where InMarketForAHouse(p);
select p;

AsEnumerable() ensures that LINQ to Objects handles all subsequent requests. So the LINQ to Entities provider (i.e. ctx.People) never sees InMarketForAHouse(..).

Now of course there are some caveats.

While the final query might yield only a few records, the query actually sent to the database might return a LOT of data.

So you need to think about what is happening here.

Ask yourself questions like: How much data am I getting from the database?

You might even be okay with *enumerating* a lot of records.

The ‘problem’ is that by default you aren’t just enumerating. The ObjectContext also does Identity resolution for each Entity, which is relatively heavy, even for entities you later discard in the LINQ to Objects query.

This particular problem is ‘easy’ to get around using a NoTracking query.

But then you get another set of problems, you can’t update the results, unless you attach them.

Anyway hopefully next time you need to ‘partition’ your query you will know more about the tradeoffs