EF 4.1 code-first: difference between EF generated sql and custom sql

I have a question about sql generated by Entity Framework and the hand-writed one. In my project, I have some entities (they aren't important really for this Q), for a simple example, when I use this code:

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

the generated sql is something like this:

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

but by this code:

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开发者_JAVA百科 JOIN
                                            Emails ON EmailsForPersons.EmailID = Emails.EmailID

I'll give the same result! What is difference between these codes? Which one have a higher performance and higher speed?

---

You can analysed and sampled the two queries to see which performs better?

See also How to clean & optimise code generated by WCF OData service?

The SQL generated by EF is very generic and needs to work in a variety of situations. For whatever reason, it is very verbose. It often has a SELECT [Col1] FROM (SELECT [Col1] ...) nested structure, and lots of CAST statements for comparisons.

Whether this is done to ensure maximum compatibility and minimum chance of someone's tricky query not being able to be translated, or whether it's done because the code that generates the SQL is much clearer and simpler, we can only guess. It's a design decision made within the Entity Framework team.

Frankly I wouldn't worry about this at all unless you test the two queries side-by-side for performance using query analyser. I would expect very minimal difference between the two.

If the performance is worse for the generated query then the simplest pattern is to write the logic inside a stored procedure and have EF call the stored procedure. This takes all the control away from EF and puts it in your hands.