jit cannot turned off for single query

[kobruleht]

To reproduce, run

await ctx.Students.FromSqlRaw("set jit to off;select * from student; set jit to on").ToListAsync();

This causes syntax error since query is wrapped to subselect.
As discussed in pgsql-general mailing list, jit causes some queries to run very slowly. It should be possible to jit off for specific query.

[roji]

Which version are you using? The above works just fine in 5.0.

[kobruleht]

I'm using .NET 5 with latest packages (5.0.3)

[roji]

Here's my code where it works:

await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

_ = await ctx.Blogs.FromSqlRaw(@"SET jit TO on; SELECT * FROM ""Blogs""; SET jit TO off").ToListAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    static ILoggerFactory ContextLoggerFactory
        => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(@"Host=localhost;Username=test;Password=test")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(ContextLoggerFactory);
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

[kobruleht]

Context contains class Toode and subclass TooteInfo without additions:

    public class Toode {
      public string Toode1 { get; set; }
      }

    public class TooteInfo : Toode    { }

DbContext contains:

        public virtual DbSet<Toode> Toodes { get; set; }
        public DbSet<TooteInfo> Tooteinfos { get; set; }

Command which causes exception:

var tooteinfo = await ctx.Tooteinfos.FromSqlRaw("set jit to off;select * from toode;set jit to on").ToListAsync();
It generates SQL

SELECT t.toode, ....  t."Discriminator", ...
	FROM (
	    set jit to off;select * from toode;set jit to on
	) AS t
	WHERE t."Discriminator" = 'TooteInfo'

Postgres error:

syntax error at or near "to" at character 1403

[roji]

This is specifically happening because you're querying a non-root type in the hierarchy. If it's acceptable to query the hierarchy root, that will work.

Opened dotnet/efcore#24157 to track on the EF side.

[roji]

Duplicate of dotnet/efcore#24157

[roji]

Another option is to send set the JIT option outside of the raw query, though that would add roundtrips. Finally, you can just wrap the whole thing in a PG function.