There is an unsaved comment in progress. You will lose your changes if you continue. Are you sure you want to reopen the work item?

45

Closed

Enable a mechanism to provide query hints

description

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.

file attachments

EF Team Triage: We are transitioning this project to GitHub (https://github.com/aspnet/EntityFramework6). As part of this transition we are bulk closing a large number
of issues in order that our new issue tracker will accurately reflect the work that our team is planning to complete on the EF6.x code base.

Moving forwards, our team will be fixing bugs, implementing small improvements, and accepting community contributions to the EF6.x code base. Larger feature work and innovation will happen in the EF Core code base (https://github.com/aspnet/EntityFramework).
Closing a feature request in the EF6.x project does not exclude us implementing the feature in EF Core. In fact, a number of popular feature requests for EF have already been implemented in EF Core (alternate keys, batching in SaveChanges, etc.).

This is a bulk message to indicate that this issue was closed and not ported to the new issue tracker. The reasons for not porting this particular issue to GitHub may include:

It was a bug report that does not contain sufficient information for us to be able to reproduce it

It was a question, but sufficient time has passed that it's not clear that taking the time to answer it would provide value to the person who asked it

It is a feature request that we are realistically not going to implement on the EF6.x code base
Although this issue was not ported, you may still re-open it in the new issue tracker for our team to reconsider (https://github.com/aspnet/EntityFramework6/issues).
We will no longer be monitoring this issue tracker for comments, so please do not reply here.

comments

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.

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.

produces

SELECT TOP (20)

[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]
FROM ( SELECT
[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