我记得在ADO.NET EF 1.0中,如果用query.Where().Where()写法生成的SQL比较弱智,就是嵌套一层一层的子查询,那么再VS2010有没有改进捏???我做个例子检验一下。
新建一个ASP.NET MVC2的工程,数据库就用SQL2000就有的Northwind,只用一个Customers表。
直接在HomeController上加Query方法,默认情况下显示全部。
代码
public ActionResult Query()
{
using (var context = new NorthwindEntities())
{
return this .View(context.Customers.ToList < Customers > ());
}
}
查询表单代码,如果textbox为空,就代表放弃这个条件。
代码
{ %>
< fieldset >
< legend > 搜索 </ legend >
< div class ="editor-label" >
城市
</ div >
< div class ="editor-field" >
<% : Html.TextBox( " city " ,this.Request[ " city " ]) %>
</ div >
< div class ="editor-label" >
公司
</ div >
< div class ="editor-field" >
<% : Html.TextBox( " company " , this.Request[ " company " ]) %>
</ div >
< div class ="editor-label" >
联系人
</ div >
< div class ="editor-field" >
<% : Html.TextBox( " contactName " , this.Request[ " contactName " ]) %>
</ div >
< p >
< input type ="submit" value ="搜索" />
</ p >
</ fieldset >
<% } %>
使用query.Where().Where()实现的代码
public ActionResult Query( string city, string company, string contactName)
{
using (var context = new NorthwindEntities())
{
var query = context.Customers.AsQueryable < Customers > ();
if ( ! string .IsNullOrEmpty(city))
{
query = query.Where < Customers > (c => c.City == city);
}
if ( ! string .IsNullOrEmpty(company))
{
query = query.Where < Customers > (c => c.CompanyName.Contains(company));
}
if ( ! string .IsNullOrEmpty(contactName))
{
query = query.Where < Customers > (c => c.ContactName.Contains(contactName));
}
return this .View(query.ToList < Customers > ());
}
}
动态使用表达式树的代码
public ActionResult Query( string city, string company, string contactName)
{
using (var context = new NorthwindEntities())
{
var parameter = Expression.Parameter( typeof (Customers));
var type = typeof (Customers);
Expression expr = Expression.Constant( true );
var methodInfo = typeof ( string ).GetMethod( " Contains " , new Type[] { typeof ( string ) });
if ( ! string .IsNullOrEmpty(city))
{
expr = Expression.And(expr,
Expression.Equal(Expression.Property(parameter, " City " ), Expression.Constant(city)));
}
if ( ! string .IsNullOrEmpty(company))
{
expr = Expression.And(expr,
Expression.Call(Expression.Property(parameter, " CompanyName " ), methodInfo, Expression.Constant(company)));
}
if ( ! string .IsNullOrEmpty(contactName))
{
expr = Expression.And(expr,
Expression.Call(Expression.Property(parameter, " ContactName " ), methodInfo, Expression.Constant(contactName)));
}
var lambda = Expression.Lambda < Func < Customers, bool >> (expr, parameter);
return this .View(context.Customers.Where < Customers > (lambda).ToList < Customers > ());
}
}
效果都是一样滴
那个这两个方法生成的SQL有什么不同捏?用SQL SERVER Profiler监视的query.Where().Where()的结果发现,在ADO.NET EF 4已经不在嵌套的子查询,已经智能滴合并了,但是用了sp_executesql这个存储过程。
代码
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[CompanyName] AS [CompanyName],
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address],
[Extent1].[City] AS [City],
[Extent1].[Region] AS [Region],
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],
[Extent1].[Phone] AS [Phone],
[Extent1].[Fax] AS [Fax]
FROM [dbo].[Customers] AS [Extent1]
WHERE ([Extent1].[City] = @p__linq__0) AND ([Extent1].[CompanyName] LIKE @p__linq__1 ESCAPE N '' ~ '' ) AND ([Extent1].[ContactName] LIKE @p__linq__2 ESCAPE N '' ~ '' ) ' ,N ' @p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000) ' , @p__linq__0 = N ' Berlin ' , @p__linq__1 = N ' %Futterkiste% ' , @p__linq__2 = N ' %Anders% '
动态组合表达式确没用存储过程,直接就一条SQL语句
代码
[ Extent1 ] . [ CustomerID ] AS [ CustomerID ] ,
[ Extent1 ] . [ CompanyName ] AS [ CompanyName ] ,
[ Extent1 ] . [ ContactName ] AS [ ContactName ] ,
[ Extent1 ] . [ ContactTitle ] AS [ ContactTitle ] ,
[ Extent1 ] . [ Address ] AS [ Address ] ,
[ Extent1 ] . [ City ] AS [ City ] ,
[ Extent1 ] . [ Region ] AS [ Region ] ,
[ Extent1 ] . [ PostalCode ] AS [ PostalCode ] ,
[ Extent1 ] . [ Country ] AS [ Country ] ,
[ Extent1 ] . [ Phone ] AS [ Phone ] ,
[ Extent1 ] . [ Fax ] AS [ Fax ]
FROM [ dbo ] . [ Customers ] AS [ Extent1 ]
WHERE (N ' Berlin ' = [ Extent1 ] . [ City ] ) AND ( [ Extent1 ] . [ CompanyName ] LIKE N ' %Alfreds% ' ) AND ( [ Extent1 ] . [ ContactName ] LIKE N ' %Maria% ' )
看来ADO.NET EF对于两种方式生成的SQL还是区别对待的,如果只有一条WHERE语句,那么直接将Lambda转换为SQL,如果是多条WHERE,还能优化SQL。所以如果是动态的AND AND AND查询,两种方式差不多,我觉得Where().Where()的方式可读性更好,如果是比较复杂的查询,比如带OR的,还要用动态组合表达式树的方式。