Creating Dynamic Queries in LINQ
Toward the bottom of the LINQ to SQL samples is a set called "advanced". I'm not sure I like that title, because "Advanced" really should read "Things you just haven't done yet".
The first set of samples gives you a glimpse into the deeper goo inside the Linq to SQL libraries. And, understand that none of this is hidden from you. Come to my CodeMash talk on IQueryProvider and you'll see that you can build the same type of capability into your own data sources.
But back to the content. Here's the entire advanced sample 1:
public void LinqToSqlAdvanced01()
{
ParameterExpression param = Expression.Parameter(typeof(Customer), "c");
Expression selector = Expression.Property(param,
typeof(Customer).GetProperty("ContactName"));
Expression pred = Expression.Lambda(selector, param);
IQueryable<Customer> custs = db.Customers;
Expression expr = Expression.Call(typeof(Queryable), "Select", new Type[] {
typeof(Customer), typeof(string) }, Expression.Constant(custs), pred);
IQueryable<string> query =
db.Customers.AsQueryable().Provider.CreateQuery<string>(expr);
System.Data.Common.DbCommand cmd = db.GetCommand(query);
Console.WriteLine("Generated T-SQL:");
Console.WriteLine(cmd.CommandText);
Console.WriteLine();
ObjectDumper.Write(query);
}
There's quite a bit happening here. The first three lines build a lambda expression that evaluates a customer and returns the contact name.
The next short block builds a method call expression that selects the return value from the Lamdba expression already created for each customer in the input sequence. That method call expression is used to create a query, typed as an IQueryable<string> (so it's a query that returns a sequence of strings).
The db.GetCommand() call is a nice debugging aid that lets you see the T-SQL generated by the Linq to SQL libraries for that query.
Finally, this sample just executes the query.
You can compose other expressions as well.
Here's a Where query:
ParameterExpression param = Expression.Parameter(typeof(Customer), "c");
Expression right = Expression.Constant("London");
Expression left = Expression.Property(param,
typeof(Customer).GetProperty("City"));
Expression filter = Expression.Equal(left, right);
Expression pred = Expression.Lambda(filter, param);
Once again, it's building code. param is a parameter expression saying that 'c' is a Customer.
Right is a constant expression, the string "London".
Left is a property evaluation expression, the string value of c.City (remember that 'c' is a customer).
Filter is a boolean expression that returns true if left (the string "London") and right (the value of c.Customer) are equal.
And finally, pred composes a lambda expression that returns true for any customer whose city is London.
See, this 'advanced' stuff, while not as simple as typing 'where City == "London" ' is not that bad. And, it's rather useful in some applications to be able to construct your query at runtime.
That's about it for today. There are other samples here, but they point out some small (but important) features that are quite simple once you see them.