CompiledQuery与纯Linq SQL生成 [英] CompiledQuery vs. Plain Linq SQL Generation

问题描述

所以我正在做一些不同的方法来分析我的SQLServer数据库。我做了香草TSQL，一个CompiledQuery和一个非编译的Linq语句。

So I was doing some profiling of different ways to hit my SQLServer database. I did vanilla TSQL, a CompiledQuery, and a noncompiled Linq statement.

按照预期，性能按照相同的顺序进行，但是当分析后两者时，我注意到好奇。

The performance went in that same order, as expected, but I noticed something curious when profiling the latter two.

CompiledQuery生成的SQL比普通的旧语句生成的SQL更好。

The SQL generated by the CompiledQuery was MUCH better than what was generated by the plain old statement.

本地SQLExpress数据库;表称为foreignTable，ColumnA为int，主键（indexed）; ColumnB是一个随机的int。

Local SQLExpress database; table is called 'foreignTable', ColumnA is int, primary key (indexed); ColumnB is a random int.

Func<testingDatabaseEntities1, int, int> GetByPK = CompiledQuery.Compile((testingDatabaseEntities1 ft, int key) 
  => (ft.foreignTable.Where(x => x.ColumnA == key).FirstOrDefault().ColumnB));

生成

SELECT 
[Project1].[ColumnB] AS [ColumnB]
FROM   ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN  (SELECT TOP (1) 
    [Extent1].[ColumnB] AS [ColumnB]
    FROM [dbo].[foreignTable] AS [Extent1]
    WHERE [Extent1].[ColumnA] = @p__linq__1 ) AS [Project1] ON 1 = 1

其中，对于生成的代码，真的不是

Which, for generated code, really isn't too terrible.

但是当我做简单的Linq语句：

But when I do the plain Linq statement:

entity.foreignTable.Where(x => x.ColumnA == searchForMe).FirstOrDefault().ColumnB

它生成：

SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[ColumnA] AS [ColumnA], 
[Limit1].[ColumnB] AS [ColumnB], 
[Limit1].[FKColumn] AS [FKColumn]
FROM ( SELECT TOP (1) 
    [Extent1].[ColumnA] AS [ColumnA], 
    [Extent1].[ColumnB] AS [ColumnB], 
    [Extent2].[FKColumn] AS [FKColumn], 
    1 AS [C1]
    FROM  [dbo].[foreignTable] AS [Extent1]
    LEFT OUTER JOIN (SELECT 
      [Table_2].[FKColumn] AS [FKColumn], 
      [Table_2].[SomeText] AS [SomeText]
      FROM [dbo].[Table_2] AS [Table_2]) AS [Extent2] ON [Extent1].[ColumnA] = [Extent2].[FKColumn]
    WHERE [Extent1].[ColumnA] = @p__linq__7
)  AS [Limit1]

这只是肮脏的。

所以我猜问题是：是否可能给实体定义Linq与CompiledQuery相同数量的SQL吸引力？

So I guess the question is: is it possible to give regular Linq to entities the same amount of SQL suckiness as a CompiledQuery?

推荐答案

您正在比较的查询不一样。第一个编译的查询返回一个属性，没有其他属性。它永远不会返回任何不同的。第二个返回一个您取消引用然后访问其属性的实体实例。当查询执行时，它无法知道您打算仅查看一个属性。

The queries you are comparing are not the same. The first, compiled query returns one property and nothing else. It can never return anything different. The second returns an entity instance which you dereference and then access a property of. When the query executes, it has no way of knowing that you intend to only look at one property.

一种方式（未经测试），您可能能够获得相同的SQL从非编译查询是将项目转换为匿名类型：

One way (untested) you might be able to get the same SQL from a non-compiled query is to project into an anonymous type:

var b = (from e in entity.foreignTable.
         where ColumnA == searchForMe
         select new 
         {
            ColumnB = e.ColumnB
         }).FirstOrDefault().ColumnB;

这篇关于CompiledQuery与纯Linq SQL生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！