EF Core, FromSqlRaw with USE HINT

user_391605 0
Viewed 674

I'm confused !
I have an ASP.Net Core 3 WebApi application, and this works fine:

var results = _context.Users.ToList();

However, if I try to add a "HINT" to the SQL...

var results = _context.Users.FromSqlRaw("SELECT * FROM t_user OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))").ToList();

... then it throws this exception...

Incorrect syntax near the keyword 'OPTION'.
Database 'HINT' does not exist. Make sure that the name is entered correctly.

Why would this SQL run successfully in SQL Server Management Studio, but get misunderstood by the WebApi ?

Update # 1

Damn. When you tell EF Core to use Raw SQL, it actually puts that SQL into a sub-clause, and this is why I'm seeing the error.

So, this is the (perfectly valid) SQL which I'm trying to run:

SELECT * FROM t_user 
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

..but, looking at SQL Server Profiler, EF Core is actually trying to this SQL, and this isn't valid...

SELECT [u].[user_id], [u].[user_name]
FROM (
    SELECT * FROM t_user 
    OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))
) AS [u]

Damn...

So how do we use Hints from EF Core ?

Update # 2

Because I'm using EF Core, I followed the instructions in this Microsoft article to add a DbCommandInterceptor to my query.

In their example, it intercepts the SQL and appends " OPTION (ROBUST PLAN)" to the string. It shows that it's trying to run this SQL:

SELECT [u].[user_id], [u].[user_name]
FROM t_user 
OPTION (ROBUST PLAN)

If I take the Microsoft example, and change it to use my HINT, then I still get the same error of it saying "Database 'HINT' does not exist."

private static void ManipulateCommand(DbCommand command)
{
    if (command.CommandText.StartsWith("-- Use hint: robust plan", StringComparison.Ordinal))
    {
        command.CommandText += " OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))";
    }
}

Ahhhh! Why can't I use a Hint ?!

asp.net-core-webapi sql-server sql
2 Answers

It seems that for some reason USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE') is treated as USE <db_name>

USE

Changes the database context to the specified database or database snapshot in SQL Server.

Then the erorr: "Database 'HINT' does not exist." has perfect sense.

Now as for ENABLE_PARALLEL_PLAN_PREFERENCE it is undocummented query hint:

https://github.com/MicrosoftDocs/sql-docs/issues/2442

Unfortunately, we have to decline on adding information about ENABLE_PARALLEL_PLAN_PREFERENCE. That is an undocumented query hint that is meant for troubleshooting purposes and is to be used at the direction of Microsoft Support. We normally do not document certain query hints/trace flags intentionally as it may lead to performance issues or other unintended consequences.

So please be cautious when setting it as default or using in production code.

You could try to use OPTION(QUERYTRACEON 8649) which will the behave same as mentioned ENABLE_PARALLEL_PLAN_REFERENCE, but it will require administrator priviliges:

private static void ManipulateCommand(DbCommand command)
{
 if (command.CommandText.StartsWith("-- Use hint: robust plan", StringComparison.Ordinal))
 {
   command.CommandText += " OPTION(QUERYTRACEON 8649)";
 }
}

To sum up: Before setting this hint in application code, I would recommend resolve the real underlying issue(maxdop/Cost Threshold for Parallelism/...)

EDIT:

Raw SQL Queries

Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. SQL queries that can be composed on begin with the SELECT keyword. Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

A trailing semicolon

On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))

On SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

user_50708790

When you tell Ef core the output of your query is an entity then EF wrap your query into that entity but if you define a Keyless Entity Types for your query, EF first execute the query and then map result into your model.

first create model like your user model:

public class KeyLessUser
        {
            public string UserId { get; set; }

            public string UserName { get; set; }

        }

And add it to DbContext with HasNoKey:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<KeyLessUser>().HasNoKey();
        }

Finally execute your query like this:

var results = _context.Set<KeyLessUser>().FromSqlRaw("SELECT * FROM t_user OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))").ToList();

Tested and works fine.

user_60231730
Related