Writing Dynamic Linq Queries in Linq-to-Entities

Believe it or not, L2E actually shines in this area

I am stubbornly plugging along with L2E, and I am even becoming kind of fond of it (in some areas). For example, I really like the way it handles dynamic queries, even more than L2E because you can pass in objects, not just strings! The syntax is a little different, but I think it is easier to understand and a better approach.


First thing is first: you have to use the System.Linq.Dynamic.dll just like with Linq-to_SQL, you can get it from the links on ScottGu's blog. Then just make sure to throw it in your bin folder (or reference it) and include this in the program:
using System.Linq.Dynamic;

how to use 'where'

Where() is a bit different here. In L2S it just takes in a string, but here I will focus on the following overload:
(extension)IQueryable<T> IQueryable<T>
  .Where(string predicate, params object[] values)

Notice it does take in a string, but also the object array. So say I want to recreate this query:
var results = db.branch
  .Where(b => b.branch_id == 5 || b.display == "Hello");

With a dynamic query I would do this:
var results = db.branch
  .Where("branch_id == @0 || display == @1", 
    new object[] {5, "Hello"});

See how easy that was? The @0 and @1 are simply replaced with the corresponding objects in the array. Also notice that the array takes in all sorts of different object types as it does not matter what you are putting through. Now obviously you aren't going to set the array manually every time, that would defeat the purpose of the dynamic query, but you can easily see how this works.

In some programs, I have even built the string part (myQuery in this example) of the query dynamically with a running count so I know what @ to place in the string; along with the string being built I would add the objects to a List<object> myObjects and simply call the final products with:
var results = db.branch
  .Where(myQuery, myObjects.ToArray());

how to use 'select'

This is identical to L2S, very simple. If you don't use a Select(), it will just grab all the full objects. But if you want to grab select parts, or change names of some stuff, it is not difficult. A simple dynamic Select could look like this:
var results = db.branch

That would just grab the branch_id and display values of the branch items. But you can also change the name of the column they are represented in which can often be useful in dynamically produced tables and such:
var results = db.branch
  .Select("new(branch_id as BRANCH,display as NINJAS)");

Those columns will now be titled 'BRANCH' and 'NINJAS' respectively. Not too tough there. I think L2E got this dynamic query thing pretty spot on.

Comments (1) -

Pingbacks and trackbacks (4)+

Add comment