ADO.NET EF 4中 query.Where().Where()和動態組合lambda實現組合查詢的不同。
我記得在ADO.NET EF 1.0中，如果用query.Where().Where()寫法生成的SQL比較弱智，就是嵌套一層一層的子查詢，那么再VS2010有沒有改進捏？？？我做個例子檢驗一下。
新建一個ASP.NET MVC2的工程，數據庫就用SQL2000就有的Northwind，只用一個Customers表。
直接在HomeController上加Query方法，默認情況下顯示全部。
代碼
[HttpGet]
public ActionResult Query()
{
using (var context = new NorthwindEntities())
{
return this.View(context.Customers.ToList<Customers>());
}
}
public ActionResult Query()
{
using (var context = new NorthwindEntities())
{
return this.View(context.Customers.ToList<Customers>());
}
}
查詢表單代碼，如果textbox為空，就代表放棄這個條件。
代碼
<% using (this.Html.BeginForm())
{ %>
<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>
<% } %>
{ %>
<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()實現的代碼
代碼
[HttpPost]
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 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>());
}
}
動態使用表達式樹的代碼
代碼
[HttpPost]
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>());
}
}
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這個存儲過程。
代碼
exec sp_executesql N'SELECT
[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%'
[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語句
代碼
SELECT
[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%')
[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的，還要用動態組合表達式樹的方式。
浙公網安備 33010602011771號