StartsWith/EndsWith/Contains with parameter fails on Azure Synapse because ESCAPE isn't supported there

[drmcclelland]

I am using Microsoft.EntityFrameworkCore.SqlServer v8.0.4 to run queries against an Azure Synapse SQL database. The SQL generated for a parameter used as the pattern for StartsWith/EndsWith/Contains automagically gets rewritten to escape any wildchars.

However, the LIKE keyword's ESCAPE clause syntax is not supported by Azure Synapse: LIKE (Transact-SQL) - SQL Server

Is there a way to suppress the generation of the ESCAPE clause?

Repro:

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

var s = "foo";
_ = await ctx.Blogs.Where(b => b.Name.Contains(s)).ToListAsync();

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\MSSQLLocalDB; Database=test; Trusted_Connection=True;")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

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

This produces the following query:

Executed DbCommand (84ms) [Parameters=[@__s_0_contains='%foo%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Name] LIKE @__s_0_contains ESCAPE N'\'

Azure Synapse would be fine with the generated SQL if it did not include ESCAPE N'\'

NOTE: I am greatly indebted to @roji for #32432 😄

[maumar]

@drmcclelland you can try:

        var s = "foo";
        _ = await ctx.Blogs.Where(b => EF.Functions.Like(b.Name, s)).ToListAsync();

which produces:

SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Name] LIKE @__s_1',N'@__s_1 nvarchar(4000)',@__s_1=N'foo'

[drmcclelland]

@maumar I appreciate that workaround! And it did work for me, but we would prefer to not have to make this change throughout the codebase for every usage of StartsWith/EndsWith/Contains.

I am curious if there is a cross-cutting way (something in the DbContext configuration or DbContextOptionsBuilder would be nice) to disable the generation of the ESCAPE clause?

Just for grins, I did try setting the SqlServerDbContextOptionsBuilder's to UseCompatibilityLevel(80) to try setting the compatibility level to SQL Server 2000 to see if that would have any effect - but it didn't 😄

[drmcclelland]

Side note: downgrading back to Microsoft.EntityFrameworkCore.SqlServer v7.0.18 produces the following SQL, which is less readable, but it does work with Azure Synapse without any changes to our codebase:

SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE (@__s_0 LIKE N'') OR CHARINDEX(@__s_0, [b].[Name]) > 0

[maumar]

currently there is no escape hatch/customization for this case. For constants we escape the constant value on the spot, for parameters we do LIKE with ESCAPE, and for any other expression type we do the CHARINDEX translation, like in 7.0

[drmcclelland]

Can I put in a request for a config/option/setting to disable the ESCAPE clause from being generated? I know you may not have very many EF users that query Azure Synapse... but there are a few of us! 😄

[roji]

We may need to provide a SQL Server context option for telling EF that Synapse is being targeted. It seems like Synapse doesn't simply represent an older SQL compatibility level (in which case we already have a user-facing API), but rather its own dialect of T-SQL, requiring its own opt-in. For example, Synapse's version of OPENJSON has a mechanism for array element identity, which other versions of SQL Server don't have.

[roji]

The regression here is specifically for StartsWith/EndsWith where the pattern is a parameter - we changed that to use LIKE in 8.0, so ESCAPE is needed. But we were already using LIKE with ESCAPE for constant patterns, where the pattern contained wildcards.

Once we have a config option for specifying targeting Synapse, we can vary the translation to not use LIKE (but fall back to LEFT/RIGHT, which is what we use for columns).

[drmcclelland]

Synapse does have some other "unique" syntax requirements, particularly related to paging as mentioned here: Query: Bring back support for UseRowNumberForPaging

Reference: Transact-SQL features supported in Azure Synapse SQL

[roji]

@drmcclelland thanks for that info, we were indeed discussing a general way for users to configure EF for targeting Synapse; this would ideally address the various T-SQL differences between Synapse and standard SQL Server / Azure SQL.

[roji]

Note: introducing UseAzureSynape() is tracked by #33816.