LINQ generating sub query for a simple order by

2020-07-10 05:49发布

问题:

I'm having trouble understanding why the SQL output has a sub-query for a simple query I wrote in LINQ. This is my code:

var list = db.User.Where(u => u.Name == somename).OrderBy(u => u.IdUser).ToList();

where somename is a parameter I'm passing at execution time.

The output SQL is:

SELECT
Project1.IdUser, 
Project1.Name
FROM (SELECT
Extent1.IdUser, 
Extent1.Name
FROM user AS Extent1
WHERE Extent1.Name = 'John' /* @p__linq__0 */) AS Project1
ORDER BY 
Project1.IdUser ASC

Should the output really have a sub-query for something that simple?

I also tried

var list = db.User.Where(u => u.Name.Equals(somename)).OrderBy(u => u.IdUser).ToList();

which generates the same output as above.

If I hard code the parameter, like:

var list = db.User.Where(u => u.Name == "John").OrderBy(u => u.IdUser).ToList();

It works as expected, generating only

SELECT
Extent1.IdUser, 
Extent1.Name
FROM user AS Extent1
WHERE 'John' /* @gp1 */ = Extent1.Name
ORDER BY 
Extent1.IdUser ASC

A few things I'm using:

  • EntityFramework 5, .NET 4.5
  • SQL Server 2012
  • Glimpse (which uses MiniProfiler) to see the SQL generated

I'm not a LINQ expert, so what am I missing here?

回答1:

As other pointed, the query results in same execution plan as yours. Entity Framework (and LINQ to Entites) is here to help you avoid writing SQL and bothering about SQL (to some extent). Under normal circumstances you don't care about SQL being generated nor you "debug" it. You just care whether the LINQ query is correct. Entity Framework (should) translates it into correct (sometimes even expected) SQL (and again, execution plan matters).

I'm not saying that you shouldn't look at SQL for performance reasons (or better to say execution plan of that query). But that should be done after you identified performance problems. And you should try to write queries simple first, that's the way to success. Of course if you know SQL you know this world of sets is different from world of object - you can write easily fairly average query in LINQ (thanks to objects world), but this will end up as nasty SQL (sets world) because of "mismatch" between worlds.