LINQ通过以下命令生成简单查询的子查询 [英] LINQ generating sub query for a simple order by

问题描述

我很难理解为什么SQL输出中有一个子查询，以我用LINQ编写的简单查询。这是我的代码：

  var list = db.User.Where（u => u.Name == somename）。 OrderBy（u => u.IdUser）.ToList（）; 
  

其中 somename 是我在执行时传递的参数。 / p>

输出SQL为：

  SELECT 
 Project1。 IdUser，
 Project1.Name 
 FROM（选择
 Extent1.IdUser，
 Extent1.Name 
 FROM用户AS Extent1 
 WHERE Extent1.Name ='John '/ * @ p__linq__0 * /）AS Project1 
 ORDER BY 
 Project1.IdUser ASC 
  

输出中是否真的应该有一个简单查询的子查询？

我也尝试过

  var list = db.User.Where（u => u.Name.Equals（somename））。OrderBy（u => u.IdUser）.ToList（）; 
  

生成与上面相同的输出。

如果我对参数进行硬编码，例如：

  var list = db.User.Where（u => u。名称==约翰）.OrderBy（u => u.IdUser）.ToList（）; 
  

它按预期工作，仅生成

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

我有几件事m使用：

• EntityFramework 5，.NET 4.5


• SQL Server 2012


• 瞥见（使用MiniProfiler）查看生成的SQL

我不是LINQ专家，那又如何呢？

解决方案

正如其他指出的那样，查询产生与您相同的执行计划。实体框架（和LINQ to Entites）在这里可以帮助您避免编写SQL并在某种程度上避免SQL的困扰。在通常情况下，您无需担心生成SQL，也无需对其进行调试。您只关心LINQ查询是否正确。实体框架（应该）将其转换为正确的（有时甚至是预期的）SQL（并且执行计划同样重要）。

我并不是说您不应该看起来在SQL上出于性能方面的考虑（或更确切地说，该查询的执行计划）。但这应该在确定性能问题后 完成。而且，您应该首先尝试简单编写查询，这就是成功的方法。当然，如果您知道SQL，就知道集合的这个世界与对象的世界是不同的-您可以在LINQ中轻松编写相当平均的查询（由于对象世界），但是由于世界之间的不匹配。

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?

解决方案

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.

这篇关于LINQ通过以下命令生成简单查询的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！