Scott Hanselman

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

January 27, '10 Comments [23] Posted in ASP.NET | ASP.NET Dynamic Data | Open Source | Programming | Source Code
Sponsored By

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!

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

facebook twitter subscribe
About   Newsletter
Sponsored By
Hosting By
Dedicated Windows Server Hosting by ORCS Web
Wednesday, January 27, 2010 10:31:18 AM UTC
So now we can add 'LINQ expression injection' to the list of security vulnerabilities to audit for...
Wednesday, January 27, 2010 10:47:36 AM UTC
Hi Scott

Where it reads : .Where("CategoryID=2 And p.UnitPrice>3")
Shouldnt it be: .Where("CategoryID=2 And UnitPrice>3") ?

And thanks for the great tip! :)

Cheers
Joao Cardoso
Wednesday, January 27, 2010 10:54:27 AM UTC
@James - that was *exactly* my first reaction!
Will Dean
Wednesday, January 27, 2010 11:18:31 AM UTC
The System.Linq.Dynamic is (to use a coarse expression) the mutts nuts. Although there is a sort-of-loss of type safety, it's actually very easy to protect against injection attacks. The Where predicate does have a version that supports interpolating parameters (similar to String.Format()). For example:

query.Where("FieldValue == @1 And OtherFieldValue.Contains(@2)",value1,value2)

If you're building the where clause on the fly, then a simple reflection check using typeof(T).GetProperty() against each of the field names in the queried object T gives that extra layer of security.
Wednesday, January 27, 2010 11:24:00 AM UTC
allowing for more dynamism. (Is that a word?)
Yes, it is :)
Wednesday, January 27, 2010 12:15:50 PM UTC
I thought one of the design goals of Linq was to actually get rid of mixing code and the string-based language which is SQL. Now you are acually re-inserting a new string-based language? Not my preferred choice.
Wednesday, January 27, 2010 1:11:03 PM UTC
Funny you mention DLINQ, as I just blogged about extending it to return statically typed results. For example, your query could use an anonymous type to transition from DLINQ back into SLINQ (Static LINQ, I guess?):

var result = items
.Select(new { Value = "" }, "new "("+Column.EntityTypeProperty.Name+" as Value"))
.Distinct().OrderBy(); // Back to normal LINQ

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


Or, as an exercise for the reader, you could support Select<string>(Column.EntityTypeProperty.Name) instead.

Cheers ~
Keith
Wednesday, January 27, 2010 1:29:26 PM UTC
This reminds me that I feel kind of bad for whomever was responsible for Entity SQL. Everyone loves LINQ (for good reason) and thus most never discover the awesomeness that is Entity SQL. I recently completed a project that I'm pretty sure would not have been possible without it.
Wednesday, January 27, 2010 1:35:15 PM UTC
Interesting post, if you are using NHibernate you can write similar dynamic queries using Linq-to-NHibernate.
My post on optimising data access queries gives an example of how this can be done.

I also found the C# 3.0 in a nutshell site was an invaluable resource. Its good to know that the same Linq knowledge will be useful across applicable across multiple ORM technologies.
Wednesday, January 27, 2010 3:16:01 PM UTC
I went with this solution for a while, but it wasn't flexible enough for me. I needed a way of persisting query expressions in plain text and parsing them into expression trees at runtime. I ended up implementing a language based on the subset of C# that can be represented with LINQ expression trees. I added support for external parameters (an expression might query a collection "$Customers" which is passed in at runtime), namespace aliasing/imports, and a couple other features. So far it's worked out great.
Wednesday, January 27, 2010 3:23:43 PM UTC
Can I suggest Linquer as a companion tool when learning LINQ. It will attempt (not always successfully) to convert existing SQL to a LINQ equivalent. I found it a great use when first learning LINQ, sometimes you get suboptimal results but it gives you a good idea.
Wednesday, January 27, 2010 4:17:19 PM UTC
Nice post.

Question. Is this dynamic query makes sense when you are using a CompiledQuery ?

Thanks.

Wednesday, January 27, 2010 6:04:25 PM UTC
James and Will - You can look at the source, the expressions are not SQL and they are effectively "compiled" into an expression. They are also constrained to certain kinds of clauses, so it's not possible to add a "Delete Tables" command to a Where Clause. That help?
Wednesday, January 27, 2010 8:52:15 PM UTC
I forgot my password & no matter what i do i can seem to get it back. Our company Rockwellautomation has me down as having a liscence
william c schwarz
Wednesday, January 27, 2010 10:33:20 PM UTC
fyi: this library is also great if you want to add a simple query language to your app without writing a parser...

everything can be done at runtime, even defining your model types (ie they don't need to exist at compile time)

very cool
Thursday, January 28, 2010 3:56:32 AM UTC
It's good to have this functionality, but at the end of the day we are just transforming the problem of creating a sql with 2 conditions into creating a compiled linq query with the same where conditions and we have to learn how to use it and run code at runtime to parse the conditions and generate the lambdas and then generate the sql that we want and the best of all, we all know how to write it.

string sql = "select * from products where CategoryID={0} And UnitPrice>{1} order by SupplierID";
IEnumerable<Product> = DataContext.ExecuteQuery<Product>(sql , new object[]{2,3});

For me:
1 - It looks far simpler and almost sure that runs faster because we don't need to precompile the dynamic text.
2 - The amount of code to type is the same:
select * from products where CategoryID={0} And UnitPrice>{1} order by SupplierID
Northwind.Products.Where("CategoryID=2 And p.UnitPrice>3").OrderBy("SupplierID")
3 - And the most important part, you know what is the sql that is being executed (this is the part the the data guys like)

But of course using the latest bits is always cooler.
Thursday, January 28, 2010 10:32:59 AM UTC
Every time I see something like this:

foreach (var item in result) {
if (item != null)


it screams to me to be re-written as


foreach (var item in result.Where(i => i != null)) {


Of course, you could write an IEnumerable<T> extensions method to make this read even better still
Thursday, January 28, 2010 10:53:03 AM UTC
> released under the Ms-PL (how is anyone supposed to know this?)

That's something I've asked myself too.

The MSDN 2008 EULA says it's OK to modify, copy and distribute, but explicitly forbids usage in any copyleft licensed code (including weak copyleft licenses like the LGPL or the Ms-RL)
\Program Files (x86)\MSDN\MSDN9.0\1033\eula.txt, 2.a.iii

Is that supposed to be a trap? That should be referenced from every single sample!

Also, it forbids usage in code for non-Windows plattforms. Wow. MSDN-Sample code in Silverlight apps would be a license violation then? They do run on MacOS. (I once hinted at the same problem with the use of the Windows-only Ms-LPL for the Silverlight control library, they eventually changed the license to the Ms-PL.)

Last time I checked, there was no licensing info on msdn.com.

User-contributed content is always Ms-PL. That's better, it only prevents usage in GPL code. But still, bummer.

What's all that supposed to be good for? Those are samples, they should be released under the most liberal licenses.
Thursday, January 28, 2010 1:15:56 PM UTC
@Scott re: James and Will's post: It helps, but it doesn't get you all of the way where you need to be. Of course, you are capable of causing havoc on a system if you know what's in the .Where() clause is going to be modified or deleted. And, of course, if you're running your app as a "trusted subsystem", you might be able to hack the .Where() clause to see stuff you shouldn't see.

@Mutts nuts guy: That's awesome. The LINQ thing, that is. I had no idea you could do that with the .Where() clause. I wonder if the Dynamic LINQ stuff is going to make it into .NET 4. Maybe it's too scary and dangerous to be anything other than a "contribution". ;-) I would like to know that someone has tried to really test it to find security holes -- James and Will do bring up good points.
Thursday, January 28, 2010 11:27:35 PM UTC
If the sportscasters can make up the word "athleticism" and abuse it constantly. Then I believe geekcasters can do the same with dynamicism. dynamism is already taken
Friday, January 29, 2010 4:17:35 AM UTC
I just started reading the book LINQ In Action, which should actually be called LINQ Demystified. This stuff here is just genius. You gotta love lambdas, extension methods and fluid interfaces. I don't care to much for the C# syntax sugar used in the query expressions. This type of meta programming shows truly the power of .NET.

Thanks for posting this.
Friday, January 29, 2010 10:03:53 AM UTC
An additional building block for people creating ORM-like libraries with Linq capability.

This could make code by Matt Warren (btw wonderful series of posts: here) a little bit more readable for standard human beings like me...

PS: @Michael Herndon, dynamisme (with a trailing e) exists in French and has nothing to do with metaphysics. It is simply used in describing a system that is dynamic... This may be the first time a French guy understands English better than native English speakers... :-)
Friday, January 29, 2010 10:29:27 AM UTC
@https://www.google.com/accounts/o8/id?id=AItOawmyKx5rtvV1vOXNTK0qJJFp124PDCPBhL4

or
@reallylongopenid.

but without the e, in English, it has to do with metaphysics or at least so wikipedia says. Thus I'm trying to understand how your understanding is better when both are just fun but moot points.
Comments are closed.

Disclaimer: The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.