EF 4.1代码优先：EF生成的sql和自定义sql之间的区别

Question

我有一个关于实体框架生成的sql和手写sql的问题。在我的项目中，我有一些实体（它们对于这个问题来说并不重要），举一个简单的例子，当我使用这段代码时：

var query = context.Employees.Select(e => new {
                PersonalCode = e.PersonelCode,
                Fname = e.Person.Fname,
                Family = e.Person.Family,
                Email = e.Person.Emails
            });

生成的sql是这样的：

SELECT 
[Project1].[EmployeeID] AS [EmployeeID], 
[Project1].[EmployeeID1] AS [EmployeeID1], 
[Project1].[PersonID] AS [PersonID], 
[Project1].[EmployeeID2] AS [EmployeeID2], 
[Project1].[PersonID1] AS [PersonID1], 
[Project1].[PersonelCode] AS [PersonelCode], 
[Project1].[Fname] AS [Fname], 
[Project1].[Family] AS [Family], 
[Project1].[C1] AS [C1], 
[Project1].[EmailID] AS [EmailID], 
[Project1].[Mail] AS [Mail]
FROM ( SELECT 
    [Extent1].[EmployeeID] AS [EmployeeID], 
    [Extent1].[PersonelCode] AS [PersonelCode], 
    [Join1].[PersonID] AS [PersonID], 
    [Join1].[Fname] AS [Fname], 
    [Join1].[EmployeeID] AS [EmployeeID1], 
    [Join3].[PersonID] AS [PersonID1], 
    [Join3].[Family] AS [Family], 
    [Join3].[EmployeeID] AS [EmployeeID2], 
    [Join5].[EmailID1] AS [EmailID], 
    [Join5].[Mail] AS [Mail], 
    CASE WHEN ([Join5].[EmailID2] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM    [dbo].[Employees] AS [Extent1]
    LEFT OUTER JOIN  (SELECT [Extent2].[PersonID] AS [PersonID], [Extent2].[Fname] AS [Fname], [Extent3].[EmployeeID] AS [EmployeeID]
        FROM  [dbo].[Persons] AS [Extent2]
        LEFT OUTER JOIN [dbo].[Employees] AS [Extent3] ON [Extent2].[PersonID] = [Extent3].[EmployeeID] ) AS [Join1] ON [Extent1].[EmployeeID] = [Join1].[PersonID]
    LEFT OUTER JOIN  (SELECT [Extent4].[PersonID] AS [PersonID], [Extent4].[Family] AS [Family], [Extent5].[EmployeeID] AS [EmployeeID]
        FROM  [dbo].[Persons] AS [Extent4]
        LEFT OUTER JOIN [dbo].[Employees] AS [Extent5] ON [Extent4].[PersonID] = [Extent5].[EmployeeID] ) AS [Join3] ON [Extent1].[EmployeeID] = [Join3].[PersonID]
    LEFT OUTER JOIN  (SELECT [Extent6].[EmailID] AS [EmailID2], [Extent6].[PersonID] AS [PersonID], [Extent7].[EmailID] AS [EmailID1], [Extent7].[Mail] AS [Mail]
        FROM  [dbo].[EmailsForPersons] AS [Extent6]
        INNER JOIN [dbo].[Emails] AS [Extent7] ON [Extent6].[EmailID] = [Extent7].[EmailID] ) AS [Join5] ON [Join5].[PersonID] = [Extent1].[EmployeeID]
)  AS [Project1]
ORDER BY [Project1].[EmployeeID] ASC, [Project1].[EmployeeID1] ASC, [Project1].[PersonID] ASC, [Project1].[EmployeeID2] ASC, [Project1].[PersonID1] ASC, [Project1].[C1] ASC

但是通过这段代码：

SELECT     Employees.PersonelCode, Persons.Fname, Persons.Family, Emails.Mail
FROM         Employees 
                        LEFT OUTER JOIN     -- or: INNER JOIN
                        Persons ON Employees.EmployeeID = Persons.PersonID 
                                LEFT OUTER JOIN
                                EmailsForPersons ON Persons.PersonID = EmailsForPersons.PersonID 
                                            LEFT OUTER JOIN
                                            Emails ON EmailsForPersons.EmailID = Emails.EmailID

我会给出相同的结果结果！这些代码有什么区别？哪一个性能更高、速度更快？

Answer 1

您可以对这两个查询进行分析和采样，看看哪个性能更好？

另请参阅如何清理和优化优化 WCF OData 服务生成的代码？

EF 生成的 SQL 非常通用，需要在各种情况下工作。无论出于何种原因，它都非常冗长。它通常具有 SELECT [Col1] FROM (SELECT [Col1] ...) 嵌套结构，以及大量用于比较的 CAST 语句。

这样做是否是为了确保最大的兼容性和最小化某人棘手的查询无法被翻译的可能性，还是因为生成 SQL 的代码更加清晰和简单，我们只能猜测。这是实体框架团队内部做出的设计决策。

坦率地说，我根本不担心这个问题，除非您使用查询分析器并排测试两个查询的性能。我预计两者之间的差异非常小。

如果生成的查询的性能较差，那么最简单的模式是将逻辑编写在存储过程中，并让 EF 调用存储过程。这将所有控制权从 EF 手中夺走，并交到您手中。