Tip 31 – How to compose L2O and L2E queries

  • Article

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

Comments

  • Anonymous
    August 11, 2009
    Indeed, I always use NoTracking queries for this reason. Don't know how much of a difference it makes but it just feels right knowing I don't load the whole change tracking machine for complex queries that don't need it!
  • Anonymous
    August 11, 2009
    Great post!And just want to add that I'm finding your recent blog posts incredibly helpful in getting to grips with EF.The recent post on fetching strategies was great - maybe sometime when you have nothing to do (!) you could put together a longer post about using the specification pattern and fetching strategies together in building DDD-style repositories based around EF. Something I'm still not very clear about.Anyway, please keep it up! Its a great blog.