Does the order of OrderBy, Select and Where clauses in Linq-to-entities matter

Question

Suppose I have a table of students with tons of columns. I want to the EF equivalent of

SELECT id,lastname,firstname 
FROM students 
WHERE coursename='Eurasian Nomads'
ORDER BY lastname,firstname

I just want a subset of the full Student model so I made a view model

public class StudentView{
    public int ID{get;set;}
    public string LastName{get;set;}
    public string FirstName{get;set;}
}

and this EF code seems to work:

List<StudentView> students=context.Students
.Where(s=>s.CourseName=="Eurasian Nomads")
.OrderBy(s=>s.LastName)
.ThenBy(s=>s.FirstName)
.Select(s=>new StudentView(){ID=s.ID,LastName=s.LastName,FirstName=s.FirstName})
.ToList();

But my question is does the order of these clauses matter at all, and if so, what sort of rules should I follow for best performance?

For example, this also seems to work:

List<StudentView> students=context.Students
.Select(s=>new StudentView(){ID=s.ID,LastName=s.LastName,FirstName=s.FirstName})
.OrderBy(s=>s.LastName)
.ThenBy(s=>s.FirstName)
.Where(s=>s.CourseName=="Eurasian Nomads")
.ToList();

Answer 1

The order in which you create your query before it's executed against the server is not relevant in most cases.

Actually one of the advantages is to be able of gradually create the query by concatenating where, order by, and other clauses.

But there are sometimes where the order can affect the generated sql.

Take the samples you provided. They both compile correctly, but the second does not actually get executed. If you try to run this query against an EF database you will get an NotSupportedException:

System.NotSupportedException: The specified type member 'CourseName' is not supported in LINQ to Entities.

The key here is that you are trying to filter the query by the CourseName property in the view model (StudentView) and not the property of the entity. So you get this error.

In the case of the first query, it correctly generates this sql:

SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[LastName] AS [LastName],
    [Extent1].[FirstName] AS [FirstName]
    FROM [dbo].[Students] AS [Extent1]
    WHERE N'Eurasian Nomads' = [Extent1].[CourseName]
    ORDER BY [Extent1].[LastName] ASC, [Extent1].[FirstName] ASC

So, as you can see the order is critical sometimes.