Skip to content

The Weekly Source Code 48 – DynamicQueryable makes custom LINQ expressions easier

January 27, 2010

NOTE: An alternative title to this post might be: "The Weekly Source Code 48: Making The Weekly Source Code 47 Suck Incrementally Less."

NOTE: This isn’t a language feature! This works on both C# and VB!

Last week I wrote a post about Dynamic Linq Query Generation in order to solve a kind of meta-programming problem. I had a site that used ASP.NET Dynamic Data and I wanted to do a LINQ query against some data. However, because I was creating a template that didn’t know enough at compile time to write a proper LINQ query that could, well, compile, I needed to creating my LINQ dynamically.

Be sure to hang in here with me, the awesome happens at the end.

I was trying to generate effectively this, at runtime

Items.Select(row => row.Property).Distinct.OrderBy(colvalue => colvalue)

And I succeeded with Tatham Oddie’s help in doing it this sub-optimal way:

protected void Page_Init(object sender, EventArgs e) {
var items = Column.Table.GetQuery();
var entityParam = Expression.Parameter(Column.Table.EntityType, "row");

// row => row.Property
var columnLambda = Expression.Lambda(Expression.Property(entityParam, Column.EntityTypeProperty), entityParam);

// Items.Select(row => row.Property)
var selectCall = Expression.Call(typeof(Queryable), "Select", new Type[] { items.ElementType, columnLambda.Body.Type }, items.Expression, columnLambda);

// Items.Select(row => row.Property).Distinct
var distinctCall = Expression.Call(typeof(Queryable), "Distinct", new Type[] { Column.EntityTypeProperty.PropertyType }, selectCall);


// colvalue => colvalue
var sortParam = Expression.Parameter(Column.EntityTypeProperty.PropertyType, "sortValue");
var columnResultLambda = Expression.Lambda(sortParam, sortParam);

// Items.Select(row => row.Property).Distinct.OrderBy(colvalue => colvalue)
var ordercall = Expression.Call(typeof(Queryable), "OrderBy",
new Type[] { Column.EntityTypeProperty.PropertyType, columnResultLambda.Body.Type },
distinctCall, columnResultLambda);

var result = items.Provider.CreateQuery(ordercall);

foreach (var item in result) {
if (item != null) DropDownList1.Items.Add(item.ToString());
}
}

"Sub-optimal" is a programmer euphemism for crappy, hard to read, code that works. But what price my immortal soul?

Fortunately, Marcin from the ASP.NET team decided to come out of his apparent blogging vow of silence (lasting 18 months, no less) to save me.

Marcin points out that there’s a sample from 2006 released under the Ms-PL (how is anyone supposed to know this?) called DynamicQueryable. You actually have this on your hard drive NOW. It’s under  Samples\1033\CSharpSamples.zip\LinqSamples\DynamicQuery\DynamicQuery in your VS install directory.

In fact, His Gu-ness blogged about this in January of 2008 giving this VB example:

Dim Northwind As new NorthwindDataContext
Dim query = From p In Northwind.Products
Where p.CategoryID = 2 And UnitPrice > 3
Order By p.SupplierID
Select p
GridView1.DataSource = query
GridView1.DataBind()

But using the DynamicQuery library you can express the same thing like this, allowing for more dynamism. (Is that a word?)

Dim Northwind As new NorthwindDataContext
Dim query = Northwind.Products
.Where("CategoryID=2 And p.UnitPrice>3")
.OrderBy("SupplierID")
GridView1.DataSource = query
GridView1.DataBind()

Again, this works great when you don’t know every input ahead of time. Marcin says:

DynamicQueryable is quite powerful and includes the following

  • Dynamic string-based querying of any LINQ provider (late-bound versions of Where, Select, OrderBy, Take, Skip, GroupBy, Any, and Count extension methods)
  • String-based mini expression language (like the “it” identifier in the sample below), including complex conditional statements and all operators
  • Dynamic creation of classes for projections

Now Marcin was able to rewrite my pile of Expression crap above into this luscious four line snippet. The DynamicQueryable magic is the "var result =" line.

protected void Page_Init(object sender, EventArgs e) {
var items = Column.Table.GetQuery();

var result = items.Select(Column.EntityTypeProperty.Name).Distinct().OrderBy("it");

foreach (var item in result) {
if (item != null) DropDownList1.Items.Add(item.ToString());
}
}

ScottGu also points to Joseph and Ben Albahari, authors of the C# 3.0 In a Nutshell book and their incredibly deep post on building type-safe predicate methods. Their PredicateBuilder is free in the LINQKit extension library and can really help out when you get even deeper into this topic.

Oh, and seriously, stop what you’re doing now and go download LINQPad, the Albahari’s most wonderful gift to us all. Then, thank them, and tell them how awesome they are.

Enjoy!


© 2009 Scott Hanselman. All rights reserved.


Comments are closed.

%d bloggers like this: