CompiledQuery vs. Plain Linq SQL Generation

Question

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.

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

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));

Generates

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.

But when I do the plain Linq statement:

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

it generates:

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]

Which is just crappy.

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

Answer 1

The compiled query is doing extra work, given it is expected you will be using that query several times. If that's what you are after, then just stick with compiled queries.

On this specific scenario, you want to do:

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

This isn't exactly the same, but it will certainly make the query more what you expect (to only retrieve the ColumnB). Doing FirstOrDefault will give you the instance, so .ColumnB is happening after the linq2sql query. This also causes a difference in behavior, as your version would fail because of the null reference, while this new query would give the ColumnB's default (null or a specific value depending on the ColumnB definition).

Answer 2

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.

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;