How Ef generating Tsql

Question

I like to know how I can improve the query generation of EF take an example

I have a DbSet ItemControllers

The Linq code is :

ItemController.FirstOrDefault(x=>x.Name=="Acl")

// The euqilent Sql generated by sql is

exec sp_executesql N'SELECT 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[ShortDescription] AS [ShortDescription], 
    [Limit1].[LongDescription] AS [LongDescription], 
    [Limit1].[DisplayName] AS [DisplayName], 
    [Limit1].[ModuleItem_Id] AS [ModuleItem_Id]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[ShortDescription] AS [ShortDescription], 
        [Extent1].[LongDescription] AS [LongDescription], 
        [Extent1].[DisplayName] AS [DisplayName], 
        [Extent1].[ModuleItem_Id] AS [ModuleItem_Id]
        FROM [dbo].[ItemControllers] AS [Extent1]
        WHERE (([Extent1].[Name] = @p__linq__0) AND ( NOT ([Extent1].[Name] IS NULL OR @p__linq__0 IS NULL))) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))
    )  AS [Limit1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'Acl'

please check the tsql why EF creating such a long query , I can simply Get the result

by following query

SELECT TOP (1) 
            [Extent1].[Id] AS [Id], 
            [Extent1].[Name] AS [Name], 
            [Extent1].[ShortDescription] AS [ShortDescription], 
            [Extent1].[LongDescription] AS [LongDescription], 
            [Extent1].[DisplayName] AS [DisplayName], 
            [Extent1].[ModuleItem_Id] AS [ModuleItem_Id]
            FROM [dbo].[ItemControllers] AS [Extent1]
            WHERE Name=@name  // few  code removed for clarity

.So my question is how I can improve the query generation of EF entity by using linq function

Answer 1

I think if you look at the query plans for the two queries (yours and the generated one) you will find very little difference. Linq is actually pretty good at generating efficient SQL, and in fact what it has added to your query will have practically zero cost.

The only thing to check about this SQL is that there is an index on the Name column.

Cheers -