Query hints (raw SQL, such as OPTIONs)

[rowanmiller]

An example from #6649 is being able to append OPTION (MAXRECURSION 2000) to the end of a query.

[roji]

While the idea of integrating arbitrary provider-specific hints in queries seems useful, treating this as some raw SQL to be tacked at the end of the query may be too limited. A specific provider's SQL dialect may require the additional SQL to be integrated somewhere in the middle, etc...

Some arbitrary annotations on the query itself which would be picked up by the provider-specific query SQL generator may cover more cases.

[rowanmiller]

@roji yeah I wasn't intending to limit it to appending text to the end of the query - I removed the word "append" from the title 😄 .

[roji]

Ok great :)

[ajcvickers]

Note from triage: we need to investigate what commonality exists here across relational database systems. For example, where can hints be attached? Is there non-SQL Server value in a query-hint only (i.e. always at the end of the query) solution?

/cc @roji @caleblloyd @ErikEJ

[roji]

Here's the PostgreSQL documentation page on querying.

I'm not sure how many things it has which can be tacked on without impacting the shape of the results (in which case they'd be beyond the scope of this issue). Some ideas that come to mind:

• ORDER BY ... NULLS { FIRST | LAST }, impacting the sort order.
• Locking the selected rows (FOR UPDATE). Users would then be able to do stuff like issue updates in raw SQL, keeping the rows locked until the end of the transaction. It's nice to be able to express the query (i.e. set of rows to lock) with EF Core LINQ.

Seems like ideally providers would be able to define an extension method on the IQueryable (like AsNoTracking()), which would be picked up by the SQL generator.

[smitpatel]

Design Notes:

• Since query/table hints are a lot provider specific, there is no good relational level API (yet). Enums for options will have issue with provider specific code in relational. String version cannot be used as is since it would be append to SQL query. So it is left upto each provider to add individual methods (or 1 common method) for users to specify hints. Details of implementation is upto the provider writers. In turn relational level will provide infrastructure for providers. One easy idea is to use QueryAnnotations (how query tagging works at present), to flow information from method to SelectExpression. It could be generalized (and combined with Tagging implementation), for select expression to have access to query annotations. Provider would give query annotations from their methods, which will reach select expression through relational layer. And while printing out SQL providers could write appropriate SQL based on the annotations. This would allow providers to implement more than just table or query level hints. (e.g. Null ordering)

• Query level hints are once per query so it can be applied to whole query. Table level hints are slightly more complicated since user may want to apply hint to one table but not the other. The API may need that granularity based on that. Further API needs to consider what would happen for the tables which are indirectly brought in through auto-include of owned entities or nav expansion. Also if API takes the type to fetch table then owned types could cause question which owned type it would apply to. General initial approach would be all or nothing. Using API to say No Lock on tables will apply to all tables in the query, however they are brought into the SQL. Based on customer feedback more granular API can be determined.

Changes to relational level remain pre-requisite for provider level work but the issue remains in backlog for now. Community contributions appreciated.

[smitpatel]

cc: @ralmsdeveloper

[roji]

Another use case: specifying per-query collation. PostgreSQL allows the following on an expression:

SELECT a < b COLLATE "de_DE" FROM test1;

or on a clumn:

SELECT a COLLATE "de_DE" < b FROM test1;

[roji]

@smitpatel agree that the way forward here is by allowing annotations to be specified by the user in the query, and for those annotations to flow to the provider's query SQL generator. The question is indeed granularity and which elements of the query expression tree can be thus annotated.

[ralmsdeveloper]

Thoughts:
What @roji spoke makes sense to me, I believe we could create a method for annotation on EFCore.Ralational to meet the following needs, following SQL Server as the basis, but the providers would be responsible for consolidating the end information .

We would have to have one more property in TableExpression (public virtual string Hint {get;set;}).

Remember that when using the annotation, this would be replicated in the "Include" tables:
x.Include(y => y.Table)

Or have one more parameter in the extension method: "NoInclude"

public static IQueryable<TEntity> With<TEntity>(
    this IQueryable<TEntity> source,
    string hint,
    string optionalParameter = null);

Sample

var query = _db
    .Blogs 
    .With("NOLOCK", "INDEX(myIndex)") // Second parameter optional
    .ToList();

Output SQL

SELECT [p].[Id], [p].[Date], [p].[Name]
FROM [Blogs] AS [p] WITH (NOLOCK, INDEX(myIndex))  

--or 

SELECT [p].[Id], [p].[Date], [p].[Name]
FROM [Blogs] AS [p] WITH (NOLOCK)  

We could also use Enum to do this:

This would prevent user typing error.

PAGLOCK, NOLOCK, READCOMMITTEDLOCK, ROWLOCK, TABLOCK ou TABLOCKX.

Sample

var query = _db
    .Blogs 
    .With(Hints.NOLOCK)
    .ToList();

For the @roji example:
#6717 (comment)

I believe we can do this in EF.Functions.

[ralmsdeveloper]

Or just:

public static IQueryable<TEntity> Hint<TEntity>(
    this IQueryable<TEntity> source,
    string hint,
    bool repeatForInclude = true);

var query = _db
    .Blogs 
    .Hint("WITH (NOLOCK)")
    .ToList();

This is left under the responsibility of the user.

In my thinking I believe that this also becomes more flexible, the user can theoretically use all available hints, since initially this would be designed only for queries!

[smitpatel]

@ralmsdeveloper - That is what we decided explicitly not to do it. Unless there is any common query hint across all providers.

Especially we cannot use any method with string parameter where string is going to be appended in SQL, that is just asking for SQL injection.
And the enum may require different values based on provider.
Hence in the current design, there will not be any method in relational provider. Providers have to write methods in whichever way they want. It could be single method with enum values or it could be individual methods for each hint.

Relational layer will just provide the storage facility in terms of query annotations. So provider methods can add query annotations, (whatever shape), and those annotations will be flowed to SQL generator by relational, where provider can look at annotations and determine what to write in SQL. Further it also avoids breaking TableExpression since there is no need to add anything like Hint in any expression. "Hints" will be available globally to SelectExpression while printing, providers can determine where each hint will be printed.

[lixaotec]

Any workaround until major updates?

I´m using ef 3.1, with some SQL background tasks that frequently update my tables, and I need ef queries to not being locked by that.

The database is configured with snapshot.

I appreciate any help or tip.

Thanks

[ajcvickers]

@lixaotec Consider using an IDbCommandInterceptor.

[lixaotec]

Will give a look @ajcvickers , thank you!

[wklingler]

Is there any chance this is going to make it into EF Core 5 so we will be able to start adding with (nolock) on our queries?

[ajcvickers]

@wklingler EF Core 5.0 is shipped. The next release will be EF Core 6.0 in November 2021. We are currently going through the planning process for 6.0 and will consider this item. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to add your vote (👍) above.

[wklingler]

That's too bad, thanks for such a quick response!

[DimitryNechaev]

Voting for the feature having ran into #23580

For now the only way is to create views on tables with nolock.

[IgorMenshikov]

I have not found anything for JOIN hints. It is something I really need:

FROM Main
JOIN Another WITH(FORCESEEK) ON Another.ID = Main.ID

[Illya-Clearpoint]

For those who are stuck between a rock and a hard place due to this issue, here is a dirty hack that may work around some cases - for me it did help with 99% of problems and hopefully it can help another lost soul.

TableHintHack.txt

To use this hack, you will need to replace the default implementation of IQuerySqlGeneratorFactory with CustomSqlServerQuerySqlGeneratorFactory, and ensure CustomSqlServerQuerySqlGenerator is registered as Transient
For example, if using application container to service Entity Framework:

public void ConfigureServices(IServiceCollection services)
{
   services.AddEntityFrameworkSqlServer()
              .AddDbContext<ECFCentralContext>((sp, options) => options.UseSqlServer(secret_squirrel_connection_string)
                                                                       .UseInternalServiceProvider(sp));
   services.AddScoped<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>();
   services.AddTransient<CustomSqlServerQuerySqlGenerator>();
}

Hoping this helps someone.

[roji]

@Illya-Clearpoint it shouldn't be necessary to register IQuerySqlGeneratorFactory as transient - normally it's scoped. You can also more easily replace a service with ReplaceService<IQuerySqlGeneratorFactory, CustomSqlServerQuerySqlGeneratorFactory>().

[ajcvickers]

@Illya-Clearpoint Database interceptors are often a less risky choice than replacing a service.

[Illya-Clearpoint]

Thanks @roji, in our case we needed to use the application container anyway so it was 6 of one, or half a dozen of the other, but yeah, using ReplaceService is just as valid of a way to do it. You're right in terms of IQuerySqlGeneratorFactory though. Thanks! 😄

[Illya-Clearpoint]

Thanks @ajcvickers.

This hack is by no means a silver bullet for this problem. In our case, I thought to make use of the Interceptors, but could not find a way to make them work at Entity level. In one severe case, we needed to add hints (UPDLOCK, HOLDLOCK) to a selection in order to avoid deadlocks across multiple concurrent processes. With this hack were finally able to apply hints at entity level.

For example:

dbContext.TableName.WithHint("UPDLOCK, HOLDLOCK").FirstOrDefaultAsync(table => table.Field1 == condition1)

[roji]

Note: table annotations are covered by #6717 - we can use this to track other remaining annotation/metadata.

Note that global query hints should already be manageable by extending the QueryCompilationContext (though above we seemed to discuss free-form, text-based hint support where we wouldn't need to add specific support for each hint type - that's slightly different).

There's also other hint types (e.g. on joins).

[roji]

Note that if we decide to use RECOMPILE when inlining parameterized collections (#34347), we'd need to implement some version of this first. Note that we don't have to do everything: just for our internal use of RECOMPILE we could do something without exposing user-facing APIs for hints.

[roji]

Note that query hint support could go in two directions:

1. A totally generic API for tacking on arbitrary strings to the query.
   • This can be a general, relational API that's implemented in whatever makes sense for each provider.
   • No need to make an EF change to support a new query hint.
   • It's the user's problem to figure out the hints, not make typos etc.; not a great API.
2. A structured API that models e.g. the SQL Server query hints specifically (docs)
   • Users get a high-quality, strongly-typed .NET API
   • This becomes a SQL Server-specific API (even though there may be some internal infrastructure that could be shared)
   • We need to evolve the API as new query hints are introduced (this is presumably a really rare thing)

We also need to keep in mind the parameterization problem. Query hints (most likely) can't be parameterized in SQL; we can use e.g. [NotParameterized] to recompile the query for different hints, or handle parameterization in the 2nd part of the query pipeline, inlining the parameter into the SQL like we do for other things (e.g. parameterized collections), and disabling the cache.

[ahdung]

@roji I need 1 enough.