Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
drmcclelland opened this issue Apr 17, 2024 · 10 comments · Fixed by #34463 or #34509
Assignees
Labels
area-query area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported regression type-bug
Milestone

Comments

@drmcclelland
Copy link

drmcclelland commented Apr 17, 2024

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
Copy link
Contributor

maumar commented Apr 17, 2024

@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
Copy link
Author

drmcclelland commented Apr 17, 2024

@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
Copy link
Author

drmcclelland commented Apr 17, 2024

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
Copy link
Contributor

maumar commented Apr 18, 2024

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
Copy link
Author

drmcclelland commented Apr 18, 2024

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! 😄

@maumar maumar changed the title How to suppress ESCAPE clause in generated SQL for StartsWith/EndsWith/Contains ? Add option to disable ESCAPE clause from being generated in alongside LIKE for StartsWith/EndsWith/Contains ? Apr 18, 2024
@roji
Copy link
Member

roji commented Apr 19, 2024

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
Copy link
Member

roji commented Apr 23, 2024

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).

@roji roji changed the title Add option to disable ESCAPE clause from being generated in alongside LIKE for StartsWith/EndsWith/Contains ? StartsWith/EndsWith/Contains with parameter fails on Synapse because ESCAPE isn't supported there Apr 27, 2024
@roji roji changed the title StartsWith/EndsWith/Contains with parameter fails on Synapse because ESCAPE isn't supported there StartsWith/EndsWith/Contains with parameter fails on Azure Synapse because ESCAPE isn't supported there Apr 27, 2024
@drmcclelland
Copy link
Author

drmcclelland commented May 1, 2024

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
Copy link
Member

roji commented May 2, 2024

@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
Copy link
Member

roji commented May 26, 2024

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

@roji roji removed the needs-design label May 26, 2024
@roji roji assigned cincuranet and unassigned roji May 26, 2024
@roji roji added this to the 9.0.0 milestone May 26, 2024
@roji roji added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Aug 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-query area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. customer-reported regression type-bug
Projects
None yet
5 participants