Expression vs. Func for Data Selection

So it happened like this...

I built a DataContext object and wanted a generic selection function called Get that would allow me to pass in the type-safe query arguments like this:

'// Pseudo-code
Get( Patient Where Patient.ID == ? )

That's cool, right? Because then, any Entity can go in the "Patient" spot, and that Entity's Property could go in the "Patient.ID" spot, and any reasonable value could go in the "?" spot.

So first, because I want to define the Entity that the get is for, I have to use a type argument. So the Get signature becomes:

'// Pseudo-code
Get(Of EntityT)(Where EntityT.Property == ?)

Right. Now, how to be able to select any Property on the EntityT we've designated? Like this:

'// Pseudo-code, but becoming more real
Get(Of EntityT)(Func(Of EntityT,Boolean) == ?)
* Func is a delegate that accepts an argument of EntityT type, and returns a boolean.

So far, so good. So, the nice thing is, Func(Of EntityT,Boolean) accepts the query value automatically, because of the nature of it wanting to return a boolean. So this definition will suffice:

'// Real code, but not complete yet...
Get(Of EntityT)(Func(Of EntityT,Boolean))

because it can be used like this:

Get(Of Patient)(Func(x) x.PatientID.Equals(0))

Get method knows because of its signature that the Func will accept an EntityT (in this case, Patient) and return a boolean. So all we do is use x as a placeholder for the EntityT object. Lambda.

See what happened? Because the Get method accepts a Func that returns a Boolean, the statement x.PatientID.Equals(0) will return the True/False-ness of the comparison.

Alright, great, we're done, right? Well, not exactly...

When using this in the DataContext, I would do something like:

Function Get(Of EntityT)(Criteria as Func(Of EntityT,Boolean)) as IQueryable(Of EntityT)
Return CreateQuery("[" + EntityNam + "]").Where(Criteria).AsQueryable

Something is wrong here already. Why do I have to cast what should be an IQueryable(Of EntityT) to an IQueryable? Because I missed something very important.

When I passed in the Criteria Func to the Where clause, Where selected the best overload based on the Criteria argument type. What was the best overload? IEnumerable. The IEnumerable Where accepts a Func. So what's wrong with that?

Well, instead of getting the delayed execution of LINQ by being able to build up my query string, the call to IEnumerable Where was causing the query to execute *before* the Where was applied, meaning that the *entire table data* was being returned in order to satisfy that one Where clause.

I noticed the side effect in performance of the form (gee, ya think?!? I'm lazy loading data from like 7 tables for this form). That means ALL THE DATA from each of the 7 tables was being brought into memory, so a filter could be applied to each of them. Wow. Bummer.

After some thought, I realized that I wanted to control the selection of the overloaded where by providing the correct argument type to Where. I want the IQueryable Where. Looking at the documentation on this, IQueryable Where signature is:

Where(Of TSource)(Expression(Of Func(Of TSource, Boolean))

Notice that?!? It wants an *Expression* of a Func. Of course! Why did I not realize that already?!?

The whole point of IQueryable is to get that delayed execution, so you can build up your query before delivering it to the store. Because I was not providing an Expression, I was instructing the query to execute, then apply my Func parameter.

So my final Get signature ended up being:

Get(Of EntityT)(Expression(Of Func(Of EntityT,Bool))

It is exactly the same as the IQueryable Where signature, took me long enough to get there!

By the way, the form load time is just a *fraction* of what it was! Beautiful!