我们在用ORM框架时，框架生成的sql语法的性能是很关键的，在对EF4.1生成的sql语法进行测试时，发现存在性能问题。
测试环境vs2010 sp1 + sql server 2008 r2 + Entity FrameWork 4.1 RC
查询1
DateTime start = DateTime.Parse("2011-03-01");
DateTime end = DateTime.Parse("2011-03-31");
DataContext.Expenses.Where(a=>a.Date >= start && a.Date <= end && a.Category.Name == "2").ToList();
生成的sql
exec sp_executesql N'SELECT
[Extent1].[ExpenseId] AS [ExpenseId],
[Extent1].[Transaction] AS [Transaction],
[Extent1].[Date] AS [Date],
[Extent1].[Amount] AS [Amount],
[Extent1].[CategoryId] AS [CategoryId]
FROM [dbo].[Expenses] AS [Extent1]
INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
WHERE ([Extent1].[Date] >= @p__linq__0) AND ([Extent1].[Date] <= @p__linq__1) AND (N''2'' = [Extent2].[Name])',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2011-03-01 00:00:00',@p__linq__1='2011-03-31 00:00:00'
查询2
DateTime start = DateTime.Parse("2011-03-01");
DateTime end = DateTime.Parse("2011-03-31");
DataContext.Expenses.Include("Category").Where(a=>a.Date >= start && a.Date <= end && a.Category.Name == "1").ToList();
生成sql
exec sp_executesql N'SELECT
[Extent1].[ExpenseId] AS [ExpenseId],
[Extent1].[Transaction] AS [Transaction],
[Extent1].[Date] AS [Date],
[Extent1].[Amount] AS [Amount],
[Extent1].[CategoryId] AS [CategoryId],
[Extent3].[CategoryId] AS [CategoryId1],
[Extent3].[Name] AS [Name],
[Extent3].[Description] AS [Description]
FROM [dbo].[Expenses] AS [Extent1]
INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
LEFT OUTER JOIN [dbo].[Categories] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[CategoryId]
WHERE ([Extent1].[Date] >= @p__linq__0) AND ([Extent1].[Date] <= @p__linq__1) AND (N''1'' = [Extent2].[Name])',N'@p__linq__0 datetime2(7),@p__linq__1 datetime2(7)',@p__linq__0='2011-03-01 00:00:00',@p__linq__1='2011-03-31 00:00:00'
查询3
Func<Expense, bool> where1 = a => a.Date >= start && a.Date <= end && a.Category.Name == "1";
DataContext.Expenses.Where(where1).ToList();
这样查询时会报错
"已有打开的与此 Command 相关联的 DataReader，必须首先将它关闭。"
查询4
Func<Expense, bool> where1 = a => a.Date >= start && a.Date <= end && a.Category.Name == "1";
DataContext.Expenses.Include("Category").Where(where1).ToList();
生成的sql
SELECT
[Extent1].[ExpenseId] AS [ExpenseId],
[Extent1].[Transaction] AS [Transaction],
[Extent1].[Date] AS [Date],
[Extent1].[Amount] AS [Amount],
[Extent1].[CategoryId] AS [CategoryId],
[Extent2].[CategoryId] AS [CategoryId1],
[Extent2].[Name] AS [Name],
[Extent2].[Description] AS [Description]
FROM [dbo].[Expenses] AS [Extent1]
INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
查询1、2是直接写的lambda表达式，查询4是动态生成lambda，这种方式是我们动态查询常用的，但这时他把所有的查询结果都返回了，如果是两个数据量非常大的表，这种返回是不可想象的，我感觉EF生成的sql有性能上的问题，希望听一下大家的意见，有没有解决方案。