为什么实体框架生成嵌套的SQL查询？ [英] Why does the Entity Framework generate nested SQL queries?

问题描述

我有这个代码

  var db = new Context（）; 
 var result = db.Network.Where（x => x.ServerID == serverId）
 .OrderBy（x => x.StartTime）
 .Take（limit）; 
  

哪个生成这个！ （注意双重选择语句）

  SELECT 
`Project1`.`Id`，
`Project1 `.`ServerID`，
`Project1`.`EventId`，
`Project1`.`StartTime` 
 FROM（SELECT 
`Extent1`.`Id`，
`Extent1`.`ServerID`，
`Extent1`.`EventId`，
`Extent1`.`StartTime` 
 FROM`Networkes` AS`Extent1` 
 WHERE `Extent1`.`ServerID` = @ p__linq__0）AS'Project1` 
 ORDER BY 
`Project1`.`StartTime` DESC LIMIT 5 
  

我应该更改什么，以便产生一个select语句？我使用MySQL和实体框架与代码优先。

更新

我有相同的结果，不管传递给 OrderBy（）方法的参数类型。

更新2：定时

 总时间（hh：mm：ss.ms）05：34：13.000 
平均时间（hh：mm：ss.ms） 25：42.000 
最大时间（hh：mm：ss.ms）51：54.000 
计数13 
第一次看到11月6日，12 19:48:19 
最后一次看到11月6日，12 20:40:22 
  

原始查询：

  SELECT`Project？`.`Id`，`Project？`.`ServerID`，`Project？`.`EventId`，`Project？`.`StartTime` FROM （SELECT`Extent？`.`Id`，`Extent？`.`ServerID`，`Extent？`.`EventId`，`Extent？`.`StartTime`，FROM`Network` AS`Extent？`WHERE`Extent ？`.`ServerID` =？）AS`Project？`ORDER BY`Project？`.`Starttime` DESC LIMIT？ 
  

我使用一个程序从MySQL中的当前进程中获取快照。

其他查询同时执行，但是当我将其更改为只有一个SELECT语句时，它不会超过一秒钟。也许我有其他事情发生了;我在问'因为我不是这么DB ... ...

更新3：解释语句

实体框架生成

 '1'，'PRIMARY'，'< derived2>'，'ALL'，NULL ，NULL，NULL，NULL，'46'，'使用filesort'
'2'，'DERIVED'，'Extent？'，'ref'，'serveridneventid，serverid'，'serveridneventid' ''，'45'，'使用where'
  

一个班轮

 '1'，'SIMPLE'，'network'，'ref'，'serveridneventid，serverid'，'serveridneventid'，'109' '45'，'使用在哪里使用filesort'
  

这是来自我的QA环境，所以我上面粘贴的时间与rowcount说明语句。我认为有大约500,000条记录与一个服务器ID相匹配。

解决方案

我从MySQL切换到SQL Server。我不想最终完全重写应用层。

解决方案

这是从逻辑上构建查询的最简单的方法表达式树。通常性能不会是一个问题。如果您遇到性能问题，您可以尝试这样的方式来实现：

  var results = db.ExecuteStoreQuery< Network> ;（
SELECT Id，ServerID，EventId，StartTime FROM Network WHERE ServerID = @ID，
 serverId）; 
 
 results = results.OrderBy（x => x.StartTime）.Take（limit）; 
  

Why does the Entity Framework generate nested SQL queries?

I have this code

    var db = new Context();
    var result = db.Network.Where(x => x.ServerID == serverId)
        .OrderBy(x=> x.StartTime)
        .Take(limit);

Which generates this! (Note the double select statement)

SELECT
`Project1`.`Id`, 
`Project1`.`ServerID`, 
`Project1`.`EventId`, 
`Project1`.`StartTime`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`ServerID`, 
`Extent1`.`EventId`, 
`Extent1`.`StartTime`
FROM `Networkes` AS `Extent1`
 WHERE `Extent1`.`ServerID` = @p__linq__0) AS `Project1`
 ORDER BY 
`Project1`.`StartTime` DESC LIMIT 5

What should I change so that it results in one select statement? I'm using MySQL and Entity Framework with Code First.

Update

I have the same result regardless of the type of the parameter passed to the OrderBy() method.

Update 2: Timed

Total Time (hh:mm:ss.ms)    05:34:13.000
Average Time (hh:mm:ss.ms)  25:42.000
Max Time (hh:mm:ss.ms)  51:54.000
Count   13
First Seen  Nov 6, 12 19:48:19
Last Seen   Nov 6, 12 20:40:22

Raw query:

SELECT `Project?`.`Id`, `Project?`.`ServerID`, `Project?`.`EventId`, `Project?`.`StartTime` FROM (SELECT `Extent?`.`Id`, `Extent?`.`ServerID`, `Extent?`.`EventId`, `Extent?`.`StartTime`, FROM `Network` AS `Extent?` WHERE `Extent?`.`ServerID` = ?) AS `Project?` ORDER BY `Project?`.`Starttime` DESC LIMIT ?

I used a program to take snapshots from the current process in MySQL.

Other queries were executed at the same time, but when I change it to just one SELECT statement, it NEVER goes over one second. Maybe I have something else that's going on; I'm asking 'cause I'm not so into DBs...

Update 3: The explain statement

The Entity Framework generated

'1', 'PRIMARY', '<derived2>', 'ALL', NULL, NULL, NULL, NULL, '46', 'Using filesort'
'2', 'DERIVED', 'Extent?', 'ref', 'serveridneventid,serverid', 'serveridneventid', '109', '', '45', 'Using where'

One liner

'1', 'SIMPLE', 'network', 'ref', 'serveridneventid,serverid', 'serveridneventid', '109', 'const', '45', 'Using where; Using filesort'

This is from my QA environment, so the timing I pasted above is not related to the rowcount explain statements. I think that there are about 500,000 records that match one server ID.

Solution

I switched from MySQL to SQL Server. I don't want to end up completely rewriting the application layer.

解决方案

It's the easiest way to build the query logically from the expression tree. Usually the performance will not be an issue. If you are having performance issues you can try something like this to get the entities back:

var results = db.ExecuteStoreQuery<Network>(
    "SELECT Id, ServerID, EventId, StartTime FROM Network WHERE ServerID = @ID", 
    serverId);

results = results.OrderBy(x=> x.StartTime).Take(limit);

这篇关于为什么实体框架生成嵌套的SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！