吉米.NET

動態查詢

有這樣一個場景：應用程序可能會提供一個用戶界面，用戶可以使用該用戶界面指定一個或多個謂詞來篩選數據。這種情況在編譯時不知道查詢的細節，動態查詢將十分有用。

在LINQ中，Lambda表達式是許多標準查詢運算符的基礎，編譯器創建lambda表達式以捕獲基礎查詢方法（例如Where、Select、Order By、Take While 以及其他方法）中定義的計算。表達式目錄樹用於針對數據源的結構化查詢，這些數據源實現IQueryable。例如，LINQ to SQL 提供程序實​​現IQueryable接口，用於查詢關係數據存儲。C#和Visual Basic編譯器會針對此類數據源的查詢編譯為代碼，該代碼在運行時將生成一個表達式目錄樹。然後，查詢提供程序可以遍歷表達式目錄樹數據結構，並將其轉換為適合於數據源的查詢語言。

表達式目錄樹在LINQ中用於表示分配給類型為Expression的變量的Lambda表達式。還可用於創建動態LINQ查詢。

System.Linq.Expressions命名空間提供用於手動生成表達式目錄樹的API。Expression類包含創建特定類型的表達式目錄樹節點的靜態工廠方法，例如，ParameterExpression（表示一個已命名的參數表達式）或MethodCallExpression（表示一個方法調用）。編譯器生成的表達式目錄樹的根始終在類型Expression的節點中，其中TDelegate是包含至多五個輸入參數的任何TDelegate委託；也就是說，其根節點是表示一個lambda表達式。下面幾個例子描述如何使用表達式目錄樹來創建動態LINQ查詢。

1.Select

下面例子說明如何使用表達式樹依據IQueryable 數據源構造一個動態查詢，查詢出每個顧客的ContactName，並用GetCommand方法獲取其生成SQL語句。

 //依據IQueryable數據源構造一個查詢
 IQueryable < Customer > custs = db.Customers;
 //組建一個表達式樹來創建一個參數
 ParameterExpression param = 
     Expression .Parameter( typeof ( Customer ), "c" );
 //組建表達式樹:c.ContactName
 Expression selector = Expression .Property(param,
     typeof ( Customer ).GetProperty( "ContactName" ));
 Expression pred = Expression .Lambda(selector, param);
 //組建表達式樹:Select( c=>c.ContactName)
 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);
 //使用GetCommand方法獲取SQL語句
 System.Data.Common. DbCommand cmd = db.GetCommand(query);
 Console .WriteLine(cmd.CommandText);

生成的SQL語句為：

SELECT [t0].[ContactName] FROM [dbo].[Customers] AS [t0]

2.Where

下面一個例子是“搭建”Where用法來動態查詢城市在倫敦的顧客。

IQueryable < Customer > custs = db.Customers;
 //創建一個參數c
 ParameterExpression param = 
     Expression .Parameter( typeof ( Customer ), "c" );
 //c.City=="London"
 Expression left = Expression .Property( param,
     typeof ( Customer ).GetProperty( "City" ));
 Expression right = Expression .Constant( "London" );
 Expression filter = Expression .Equal(left, right);
 Expression pred = Expression .Lambda(filter, param) ;
 //Where(c=>c.City=="London")
 Expression expr = Expression .Call( typeof ( Queryable ), "Where" ,
     new Type [] { typeof ( Customer ) }, 
     Expression .Constant(custs) , pred);
 //生成動態查詢
 IQueryable < Customer > query = db.Customers.AsQueryable()
    .Provider.CreateQuery< Customer >(expr);

生成的SQL語句為：

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] WHERE [t0].[City] = @p0
 -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

3.OrderBy

本例既實現排序功能又實現了過濾功能。

IQueryable < Customer > custs = db.Customers;
 //創建一個參數c
 ParameterExpression param =
    Expression .Parameter( typeof ( Customer ), "c" );
 //c.City=="London"
 Expression left = Expression .Property( param,
     typeof ( Customer ).GetProperty( "City" ));
 Expression right = Expression .Constant( "London" );
 Expression filter = Expression .Equal(left, right);
 Expression pred = Expression .Lambda(filter, param) ;
 //Where(c=>c.City=="London")
 MethodCallExpression whereCallExpression = Expression .Call(
     typeof ( Queryable ), "Where" ,
     new Type [] { typeof ( Customer ) },
     Expression .Constant(custs) , pred);
 //OrderBy(ContactName => ContactName)
 MethodCallExpression orderByCallExpression = Expression .Call(
     typeof ( Queryable ), "OrderBy" ,
     new Type [] { typeof ( Customer ), typeof ( string ) }, 
    whereCallExpression, Expression .Lambda ( Expression .Property 
    (param, "ContactName" ), param));
 //生成動態查詢
 IQueryable < Customer > query = db.Customers.AsQueryable() 
    .Provider.CreateQuery< Customer >(orderByCallExpression);

下面一張截圖顯示了怎麼動態生成動態查詢的過程

生成的SQL語句為：

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] WHERE [t0].[City] = @p0
 ORDER BY [t0].[ContactName]
 -- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

4.Union

下面的例子使用表達式樹動態查詢顧客和僱員同在的城市。

 //e.City
 IQueryable < Customer > custs = db.Customers;          
 ParameterExpression param1 = 
 Expression .Parameter( typeof ( Customer ), "e" );
 Expression left1 = Expression .Property(param1, 
     typeof ( Customer ).GetProperty( "City " ));
 Expression pred1 = Expression .Lambda(left1, param1);
 //c.City
 IQueryable < Employee > employees = db.Employees;
 ParameterExpression param2 = 
 Expression .Parameter( typeof ( Employee ), "c" );
 Expression left2 = Expression .Property(param2, 
     typeof ( Employee ).GetProperty( "City" ));
 Expression pred2 = Expression .Lambda(left2, param2);
 //Select(e=>e.City)
 Expression expr1 = Expression .Call( typeof ( Queryable ), "Select" , 
     new Type [] { typeof ( Customer ), typeof ( string ) }, 
     Expression .Constant(custs), pred1);
 //Select(c=>c.City)
 Expression expr2 = Expression .Call( typeof ( Queryable ), "Select" , 
     new Type [] { typeof ( Employee ), typeof ( string ) }, 
     Expression .Constant(employees), pred2);
 //生成動態查詢
 IQueryable < string > q1 = db .Customers.AsQueryable() 
    .Provider.CreateQuery< string >(expr1);
 IQueryable < string > q2 = db.Employees.AsQueryable() 
    .Provider.CreateQuery< string >(expr2);
 //並集
 var q3 = q1. Union(q2);

生成的SQL語句為：

SELECT [t2].[City]
 FROM (
     SELECT [t0].[City] FROM [dbo].[Customers] AS [t0]
     UNION 
    SELECT [t1].[City] FROM [dbo].[Employees] AS [t1 ] 
    ) AS [t2]