Dude where is my query going to run?

One of the key decisions with LINQ to Entities (L2E) is that all predicates in an L2E expression must be converted into store expressions, so that the store does all the heavy lifting.

This means for example if you try the following:

var matches = from e in ctx.Employees
              where IsTopPerformer(e) && e.FirstName.StartsWith(“C”)
              select e;

You will get a runtime error, because there is no mapping available for the IsTopPerformer(..) function.

Notice the error is a runtime error, the above code will happily compile, because at compile time there is no way of knowing that the valid CLR function IsTopPerformer(..) has no mapping. So when you try to enumerate matches this is what you end up seeing:

If you try to split the query like this:

var remotematches = from e in ctx.Employees
                    where e.FirstName.StartsWith(“C”)
                    select e;

var matches = from e in remotematches
              where IsTopPerformer(e)
              select e;

You still get the same problem, because remotematches is still IQueryable<Employee> and as such L2E will still attempt to map IsTopPerformer(..) to a store expression.

So the key is to call AsEnumerable() . This forces the remote (i.e. L2E) query to be invoked, so if we filter some more it is handled by LINQ to Objects in memory:

var remotematches = from e in ctx.Employees
                    where e.FirstName.StartsWith(“C”)
                    select e;

var matches = from e in remotematches.AsEnumerable()
              where IsTopPerformer(e)
              select e;

All this makes perfect sense if you consider L2E’s close relationships with eSQL. In eSQL you have no CLR functions you can call, you simply have those that are defined by the provider manifest and standard relational algebra.

However L2E is not eSQL and we have opportunities to leverage ‘extensions’ or simply standard CLR functions, quite naturally.

So would it be useful to for L2E to provide a way for our customers to do this in one step in the future?

Perhaps something like this:

var matches = from e in ctx.Employees
     where Lift(IsTopPerformer(e)) && e.FirstName.StartsWith(“C”)
     select e;

In this example Lift(..) is some as yet unrealized and unpromised well known function that L2E could in the future allow and automatically lift so it is evaluated outside the database.

I.e. it is essentially doing the work of splitting one logical query into a pair of remote and local queries for you auto-magically.

Unfortunately, as is routinely the case, the devil is in the detail.

What would you do if you encountered this?

var matches = from e in ctx.Employees
     where Lift(IsTopPerformer(e)) || e.FirstName.StartsWith(“C”)
     select e;

You can’t filter in the database and then on the client, because you would filter out all Top Performers who don’t start with “C”. So your only real option is to do all the filtering on the client? Argh!!!

So I think that means we are saying that Lift(..) could work when used by itself or when used in an AND, but not when used with an OR.

So perhaps the status quo is the best we can hope for after all?

What do you think is the remote/local query pattern simple enough?