Enable a mechanism to provide query hints


Neither the Linq extensions to C# nor Entity Framework provides any way to pass a query hint to the underlying data provider. For database programming, this is a fundamental shortcoming of the Microsoft development stack. There is no way, on a per-query basis to pass a simple locking hint (NOLOCK, HOLDLOCK, etc.).
The only workaround is to use TransactionScope objects to fence specific code boundaries, which essentially executes SET ISOLATION statements on the connection. But there is no way, for example, to specify READPAST or FORCESEEK query options, which are essential to managing lock contention in high-volume systems. Obviously, this also means there is no way to provide index hints.

Short Term: Add a monadic With() function to Entity Framework to transmit query hints to Sql Server:
                            (from e in Db.ExtCompany.With(QueryHint.ReadPast) where e.Company == companyId select e).Any();

                                       Longer Term: Add a “with” keyword to C# for use in Linq to supply a query-provider-specific hint to the underlying provider:
                            (from e in Db.ExtCompany with ReadPast, ForceSeek where e.Company == companyId select e).Any();

file attachments


davidlars99 wrote Feb 7, 2013 at 3:12 PM

It would be fantastic to add support for query/table hints. Our team has to shy away from EF because of this limitation..

RoMiller wrote Feb 20, 2013 at 5:22 PM

EF Team Triage: We agree that this would be a good scenario to enable. Taking into account where we are in the EF6 release along with the size and the impact of this feature our team is not planning to implement it in EF6. Therefore, we are moving it to the Future release to reconsider in the next release.

ZBo wrote May 10, 2013 at 5:21 PM

There are two work-arounds.

1) Use TransactionScope objects to execute SET ISOLATION statements on the connection.

2) Create a view and apply the join hints to the FROM clause inside the view. Example ViewWrapperTest.SQL attached.

sporty81 wrote Jan 1, 2014 at 7:27 PM

This is a huge shortcoming for sure.

brebok wrote Apr 15, 2014 at 1:02 AM

We are having an issue where SQL Server parameter sniffing is causing for inefficient query plans to be used to execute certain queries. It would be great to have an API in Entity Framework to specify OPTION (OPTIMIZE FOR(@param1 = <val_1>)) or OPTION(OPTIMIZE FOR UNKNOWN).

Without it, I am considering a TVF wrapper over a view with the OPTIMIZE statement or modifying the EF query so that it contains the filter value directly in the generated SQL query (that is not replaced with a SQL bind variable). The latter solution defeats the purpose and benefits of EF query compilation and SQL server query compilation.

seabizkit wrote Dec 11, 2014 at 9:11 AM

this simple query is producing weird resulsts

var query = (from c in context.Cities
            join country in context.Countries on c.CountryId equals country.CountryId
                     where (c.Name.StartsWith(q) | country.Name.StartsWith(q)) 
            orderby c.Name
            select new CityDTO()
                CityId = c.CityId,
                CountryId = country.CountryId,
                CountryName = country.Name,
                CreatedBy = c.CreateUserId ?? 0,
                CreatedDate = c.CreatedDate,
                GeoLocation = c.GeoLocation,
                Name = c.Name,
                Province = c.Province,
                LastModifiedBy = c.UpdateUserId ?? 0,
                LastModifiedDate = c.UpdatedDate


[Project1].[CityId] AS [CityId], 
[Project1].[CountryId] AS [CountryId], 
[Project1].[Name1] AS [Name], 
[Project1].[C1] AS [C1], 
[Project1].[CreatedDate] AS [CreatedDate], 
[Project1].[GeoLocation] AS [GeoLocation], 
[Project1].[Name] AS [Name1], 
[Project1].[Province] AS [Province], 
[Project1].[C2] AS [C2], 
[Project1].[UpdatedDate] AS [UpdatedDate]
FROM ( SELECT [Project1].[CityId] AS [CityId], [Project1].[Name] AS [Name], [Project1].[CreatedDate] AS [CreatedDate], [Project1].[UpdatedDate] AS [UpdatedDate], [Project1].[Province] AS [Province], [Project1].[GeoLocation] AS [GeoLocation], [Project1].[CountryId] AS [CountryId], [Project1].[Name1] AS [Name1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], row_number() OVER (ORDER BY [Project1].[Name] ASC) AS [row_number]
        [Extent1].[CityId] AS [CityId], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[CreatedDate] AS [CreatedDate], 
        [Extent1].[UpdatedDate] AS [UpdatedDate], 
        [Extent1].[Province] AS [Province], 
        [Extent1].[GeoLocation] AS [GeoLocation], 
        [Extent2].[CountryId] AS [CountryId], 
        [Extent2].[Name] AS [Name1], 
        CASE WHEN ([Extent1].[CreateUserId] IS NULL) THEN 0 ELSE [Extent1].[CreateUserId] END AS [C1], 
        CASE WHEN ([Extent1].[UpdateUserId] IS NULL) THEN 0 ELSE [Extent1].[UpdateUserId] END AS [C2]
        FROM  [dbo].[City] AS [Extent1]
        INNER JOIN [dbo].[Country] AS [Extent2] ON [Extent1].[CountryId] = [Extent2].[CountryId]
        WHERE ([Extent1].[Name] LIKE 'fran%' ESCAPE N'~') OR ([Extent2].[Name] LIKE 'fran%' ESCAPE N'~')
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[Name] ASC

if you remove the ESCAPE N'~' i get the desired result....

please add an option to add OPTION(OPTIMIZE FOR UNKNOWN) or something like that... into the generated SQL