LINQ体验(17)——LINQ to SQL语句之动态查询

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]

ID标识

在前面这一点没有说到，在这里作为高级特性单独说下ID标识。

这个例子说明我们存储一条新的记录时候，ContactID作为主键标识，系统自动分配，标识种子为1，所以每次自动加一。

//ContactID是主键ID，插入一条数据，系统自动分配ID
Contact con = new Contact()
{
    CompanyName = "New Era",
    Phone = "(123)-456-7890"
};
db.Contacts.InsertOnSubmit(con);
db.SubmitChanges();

版权声明：本文为博主http://www.zuiniusn.com原创文章，未经博主允许不得转载。

LINQ体验(17)——LINQ to SQL语句之动态查询

标签：