时间戳

首页 > 解决方案 > 为什么实体框架会生成带有重复 SQL 的嵌套 SELECT 查询？

c# - 为什么实体框架会生成带有重复 SQL 的嵌套 SELECT 查询？

问题描述

我正在使用实体框架日志记录来查看我的查询生成的 SQL。在许多情况下，生成的 SQL 具有在与原始查询相同的子查询上运行的 SELECT 语句。例如：

以下 EF 代码：

context.PodcastItems
.Where(p => p.ID == sourceFeedUpdateItemId)
.Select(p => new NewPodcastItemSubsetDTO
{
   ID = p.ID,
   ItemID = p.ItemID,
   ItemTitle = p.ItemTitle,
   ItemSummary = p.ItemSummary,
   PermalinkUrl = p.PermalinkUrl,
   ItemPublishedDate = p.ItemPublishedDate,
   AudioFileUrl = p.AudioFileUrl,
   FileSize = p.FileSize,
   Duration = p.Duration
}).SingleOrDefault();

生成以下 SQL：

SELECT
    [Limit1].[ID] AS [ID],
    [Limit1].[ItemID] AS [ItemID],
    [Limit1].[ItemTitle] AS [ItemTitle],
    [Limit1].[ItemSummary] AS [ItemSummary],
    [Limit1].[PermalinkUrl] AS [PermalinkUrl],
    [Limit1].[ItemPublishedDate] AS [ItemPublishedDate],
    [Limit1].[AudioFileUrl] AS [AudioFileUrl],
    [Limit1].[FileSize] AS [FileSize],
    [Limit1].[Duration] AS [Duration]
    FROM ( SELECT TOP (2)
        [Extent1].[ID] AS [ID],
        [Extent1].[ItemID] AS [ItemID],
        [Extent1].[ItemTitle] AS [ItemTitle],
        [Extent1].[ItemSummary] AS [ItemSummary],
        [Extent1].[PermalinkUrl] AS [PermalinkUrl],
        [Extent1].[AudioFileUrl] AS [AudioFileUrl],
        [Extent1].[FileSize] AS [FileSize],
        [Extent1].[Duration] AS [Duration],
        [Extent1].[ItemPublishedDate] AS [ItemPublishedDate]
        FROM [dbo].[PodcastItems] AS [Extent1]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Limit1]

这个代码：

string content = context.PodcastItems
.Where(p => p.ID == sourceFeedUpdateItemId)
.Select(p => p.ItemContentCleansed)
.SingleOrDefault();

产生：

SELECT
    [Limit1].[ItemContentCleansed] AS [ItemContentCleansed]
    FROM ( SELECT TOP (2)
        [Extent1].[ItemContentCleansed] AS [ItemContentCleansed]
        FROM [dbo].[PodcastItems] AS [Extent1]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Limit1]
SELECT
    [Limit1].[ItemContentCleansed] AS [ItemContentCleansed]
    FROM ( SELECT TOP (2)
        [Extent1].[ItemContentCleansed] AS [ItemContentCleansed]
        FROM [dbo].[PodcastItems] AS [Extent1]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Limit1]
SELECT
    [Limit1].[ItemContentCleansed] AS [ItemContentCleansed]
    FROM ( SELECT TOP (2)
        [Extent1].[ItemContentCleansed] AS [ItemContentCleansed]
        FROM [dbo].[PodcastItems] AS [Extent1]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Limit1]
SELECT
    [Limit1].[ItemContentCleansed] AS [ItemContentCleansed]
    FROM ( SELECT TOP (2)
        [Extent1].[ItemContentCleansed] AS [ItemContentCleansed]
        FROM [dbo].[PodcastItems] AS [Extent1]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Limit1]
SELECT
    [Limit1].[ItemContentCleansed] AS [ItemContentCleansed]
    FROM ( SELECT TOP (2)
        [Extent1].[ItemContentCleansed] AS [ItemContentCleansed]
        FROM [dbo].[PodcastItems] AS [Extent1]
        WHERE [Extent1].[ID] = @p__linq__0
    )  AS [Limit1]

当然似乎花费了很多额外的周期来完成......什么都没有？

为什么会发生这种情况？

标签： c#sqlsql-serverentity-frameworkentity-framework-6

解决方案

检查 EDMX 文件，也许你的实体是DefiningQuery这样定义的：

<!-- SSDL content -->
<edmx:StorageModels>
  <Schema Namespace="SchoolModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">
    <EntityContainer Name="SchoolModelStoreContainer">
       <EntitySet Name="GradeReport" EntityType="SchoolModel.Store.GradeReport">
          <DefiningQuery>
            SELECT CourseID, Grade, FirstName, LastName
            FROM StudentGrade
          </DefiningQuery>
      </EntitySet>

您只需将其替换DefiningQuery为EntitySet：

 <EntitySet Name="Course" EntityType="SchoolModel.Store.Course" store:Type="Tables" Schema="dbo" />

阅读更多文章。

推荐阅读