Dynamic String based Queries in LINQ - Dynamic Expression API
Version: Visual Studio 2008 Beta 2
The Dynamic Expression API extends the core LINQ API with the ability to dynamically create string based queries that are constructed at run-time. The API provides
- Dynamic parsing of strings to produce expression trees (the ParseLambda and Parse methods),
- Dynamic creation of “Data Classes” (the CreateClass methods), and
- Dynamic string-based querying through LINQ providers (the IQueryable extension methods).
The post uses a LINQ to SQL entity model for the NorthWind database in VS 2008, We create an instance of this model like so:
NorthwindDataContext context = new NorthwindDataContext();
I normally would write a query to return all customers ordered by the ContactName in descending order like so:
var x = context
.Customers
.OrderByDescending(c => c.ContactName);
If instead, I wanted to define the Sort column and Sort direction in a string, I could rewrite the query, with the help of the Dynamic Expression API, like so:
string sortExpression = "ContactName DESC";
var x1 = context
.Customers
.OrderBy(sortExpression);
The SQL generated in both cases will be
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0] 4: ORDER BY [t0].[ContactName] DESC
Moving on to another example, If I had a query like so,
DateTime myDate = Convert.ToDateTime("7/26/1996");
var y = context
.Orders
.Where(a => (a.Customer.Country == "Switzerland") && (a.OrderDate < myDate))
.OrderBy(o=> o.OrderDate)
.Select(o => new { o.Customer.ContactName, o.Customer.Country, o.OrderDate });
I could use the Dynamic Expression API to rewrite it like this:
var y1 = context
.Orders
.Where("Customer.Country == @0 and OrderDate < @1", "Switzerland", myDate)
.OrderBy("OrderDate")
.Select("new(Customer.ContactName,Customer.Country, OrderDate)");
Note that, in the query above, the shape of the result is specified as a string. Note also that I have defined what are known as substitution values in the Where statement.
The SQL generated in both cases would be:
exec sp_executesql N'SELECT [t1].[ContactName], [t1].[Country], [t0].[OrderDate]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN [dbo].[Customers] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE ([t1].[Country] = @p0) AND ([t0].[OrderDate] < @p1)
ORDER BY [t0].[OrderDate]' 6: N'@p0 nvarchar(11),@p1 datetime'
@p0=N'Switzerland',
@p1='1996-07-26 00:00:00:000'
To use the Dynamic Expression API, add the Dynamic.cs class to your project and import the System.Linq.Dynamic namespace.
To learn more, download the LINQ and language samples for Visual Studio 2008 Beta 2 zip file located at the Visual Studio 2008 samples page.