Dynamic LINQ to SQL
Friday, July 25th, 2008I came across the following statement about LINQ to SQL on a developer forum the other day:
“I think the general consensus is this: it’s good if you know the query beforehand and what type of object you’re going to get back on execution, but if the query is dynamic and/or large then it’s not well suited.”
I thought I would kick off this blog by challenging this statement with some simple examples:
The contributor was partly referring to his belief that it was not possible to do anything similar to dynamic SQL using LINQ to SQL and in fact posted a challenge to do in LINQ to SQL the equivalent of the following dynamic SQL:
string dynamicSQL = ” SELECT ” + <someCols> + ” FROM ” + <someTable> + ” WHERE ” + <someCondition> + ” ORDERBY ” + <orderBy>;
So we will look at how this can be done using LINQ to SQL.
Before looking at this it’s useful to observe that when you dynamically create the <someCondition> for the WHERE clause you would need to distinguish between types when constructing it dynamically. For example:
WHERE Quantity = 3 AND City=’London’ (Assuming Quantity is a number of some type)
You need to know that Quantity is a number and City is a string in order to correctly construct the SQL WHERE clause. What I am getting at is that when constructing dynamic SQL you have to be somewhat aware of what types you are dealing with.
The first thing to remember is that when we construct a LINQ to SQL query, that query is in fact stored as an expression tree. LINQ to SQL uses this expression tree to generate SQL which is sent to the database.
So the starting point for doing dynamic LINQ to SQL is to look at the System.Linq.Expressions namespace.
The System.Linq.Expressions namespace contains classes, interfaces and enumerations that enable language-level code expressions to be represented as objects in the form of expression trees. It enables us to directly manipulate expression trees at runtime. Manipulating expression trees at runtime allows us to create LINQ to SQL queries dynamically.
However, creating LINQ to SQL expression trees directly is not very convenient and is a none trivial task. What would be nice is to have the ability to convert a string representation of a LINQ to SQL query that can be parsed into an expression tree.
As it happens, there is a Dynamic Query Library written in C# that does precisely this and it is available at:
C# Dynamic Query Library (included in the \LinqSamples\DynamicQuery directory)
(The samples also include the NorthwindMapping project which contains the mapping classes for the Northwind database. The examples here are against this mapping.)
If you look in this library (System.Linq.Dynamic) you will see that it makes extensive use of the System.Linq.Expressions and System.Reflection namespace to implement its functionality.
Using this library we are able to construct a potentially fully dynamic LINQ to SQL query.
I will use as my example the LINQ query:
var query =
from c in db.Customers
where c.City == “London” && c.Orders.Count >= 10
select new { Name = c.CompanyName, Phone = c.Phone };
expressed here in the C# LINQ syntax. Or:
var query =
db.Customers
.Where(c => c.City == “London” && c.Orders.Count >= 10)
.OrderBy(c=>c.CompanyName)
.Select(c => new { Name = c.CompanyName, Phone = c.Phone });
expressed here using extension methods. These two queries equate to the same thing.
The System.Linq.Dynamic namespace basically provides a number of overloads to the extension methods for the IQueryable<T> and IQueryable interfaces that accept string representations of lambda expressions. For example the signatures for the Where extension methods are:
public static IQueryable<T> Where<T>(
this IQueryable<T> source,
string predicate, params object[] values) {}
and
public static IQueryable Where<T>(
this IQueryable source,
string selector, params object[] values) {}
Of course we must make these extensions available by including the using statement:
using System.Linq.Dynamic;
Using these overloaded extension methods we can re-write the above query using string representations like so:
string connString =
@”Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated
Security=True”;
Northwind db = new Northwind(connString);
Assembly asm = Assembly.GetAssembly(typeof(Northwind));
var q = db.GetTable(asm.GetType(“NorthwindMapping.Customer”))
.Where(“City == @0 and Orders.Count >= @1″, “London”, 10)
.OrderBy(“CompanyName, City”)
.Select(“New(CompanyName as Name, Phone)”);
I have used the db.GetTable(asm.GetType(“NorthwindMapping.Customer”))
statement to get the mapping object dynamically too, however the extension methods have taken care of the rest.
Everything is expressed as strings with the exception that the parameters for the Where clause need to be the correct type, or at least of types that are implicitly convertible to the correct type. However as I mentioned before, you would need some type awareness when constructing an SQL WHERE clause too.
I could put this into a general purpose method:
public static IQueryable FullDynamic(
System.Data.Linq.DataContext dc,
string tableName,
string orderBy,
string select,
string wherePredicate,
params object[] whereValues
)
{
IQueryable q = dc.GetTable(
Assembly.GetAssembly(dc.GetType()).GetType(tableName))
.Where(wherePredicate, whereValues)
.OrderBy(orderBy)
.Select(select);
return q;
}
And call it thus:
IQueryable q = FullDynamic(
db,
“NorthwindMapping.Customer”,
“CompanyName, City”,
“New(CompanyName as Name, Phone)”,
“City == @0 and Orders.Count >= @1″,
“London”, 10);
foreach (System.Linq.Dynamic.DynamicClass item in q)
{
Console.WriteLine(item);
}
I would need to do a little more work on this to enable changing the direction of the order by, but I think that would be about all to answer the challenge fully.
As you can see the resulting items from the dynamic query inherit from the System.Linq.Dynamic.DynamicClass abstract type.
foreach (System.Linq.Dynamic.DynamicClass item in query2)
{
PropertyInfo[] props = item.GetType().GetProperties(BindingFlags.Instance |
BindingFlags.Public);
StringBuilder sb = new StringBuilder();
sb.Append(“{”);
for (int i = 0; i < props.Length; i++)
{
if (i > 0) sb.Append(“, “);
sb.Append(props[i].Name);
sb.Append(“=”);
sb.Append(props[i].GetValue(item, null));
}
sb.Append(“}”);
Console.WriteLine(sb.ToString());
}
This demonstrates that you can use reflection to inspect the resulting object graph at runtime – nothing new here, just straight forward use of reflection. In fact this code is taken from the ToString() overload of the System.Linq.Dynamic.DynamicClass abstract type.
I hope this is helpful. In my next article I will look at the other issue raised, of large or complex queries in LINQ to SQL.