动态构建LINQ(Dynamic String based Queries in LINQ - Dynamic Expression API)

最新推荐文章于 2024-03-26 09:49:57 发布

转载最新推荐文章于 2024-03-26 09:49:57 发布 · 1k 阅读

LINQ 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何使用动态表达式API在LINQ中通过字符串定义查询条件，实现灵活的运行时查询操作，包括创建数据类、解析字符串为表达式树以及通过LINQ提供者进行字符串驱动的查询。

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' 
 