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>());
            }
        }

 

查询表单代码，如果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>

    <% } %>

 

使用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>());
            }
        }

 

 

动态使用表达式树的代码

        [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>());
            }
        }

 

 

效果都是一样滴

 那个这两个方法生成的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%'

 

 

动态组合表达式确没用存储过程，直接就一条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%')

 

 

看来ADO.NET EF对于两种方式生成的SQL还是区别对待的，如果只有一条WHERE语句，那么直接将Lambda转换为SQL，如果是多条WHERE，还能优化SQL。所以如果是动态的AND AND AND查询，两种方式差不多，我觉得Where().Where()的方式可读性更好，如果是比较复杂的查询，比如带OR的，还要用动态组合表达式树的方式。