LIKE operator in FromSqlInterpolated/FromSqlRaw not working, but fluent API or LINQ expressions do

Question

I'm wetting my feet with EF Core on .NET 3.1. I've the following code excerpt:

public static async Task<Task> getWithName(HttpContext c) {
            var name = c.Request.RouteValues["name"].ToString();

            // with API - WORKS!
            var authors = await DB.Authors.Where(a => a.first_name.Contains(name)).ToListAsync();
            // with raw SQL interpolated - BROKEN
            var authors2 = await DB.Authors.FromSqlInterpolated($"SELECT * FROM author WHERE first_name like '%{name}%'").ToListAsync();
            // with raw SQL parametrized - BROKEN
            var authors3 = await DB.Authors.FromSqlRaw("SELECT * FROM author WHERE first_name like '%{0}%'", name).ToListAsync();
            // with LINQ expressions - WORKS!
            var authors4 = await (from a in DB.Authors where a.first_name.Contains(name) select a).ToListAsync();

            c.Response.ContentType = "application/json";
            return c.Response.WriteAsync(Serialize(authors));
        }

beside the missing context around this method, what confuses me is that both the fluent API version and the LINQ query expression version work as expected, returning the 12 items in the DB. On the opposite both the interpolated and raw SQL fail returning 0 items.

Plese, mind: it is not that I get an exception or any erorr. They simply return 0 results as if the query was wrong.

To make a raw test, I've just put a breakpoint, copied the name value and performed the query straight into pgAdmin. The query works as expected, returning the same 12 items.

This is the (rather complex) debug output of the 4 queries you see in the code:

#this is the DB init debug output

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.4 initialized 'AuthorContext' using provider 
'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None

#this is the fluent API query, which works

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (14ms) [Parameters=[@__name_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT a.id, a.first_name, a.last_name, a.nationality
      FROM public.author AS a
      WHERE (@__name_0 = '') OR (STRPOS(a.first_name, @__name_0) > 0)

#this is the raw SQL, which fails

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[p0='?'], CommandType='Text', CommandTimeout='30']
      SELECT * FROM author WHERE first_name like '%@p0%'

#this is the interpolated SQL, which fails too

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[p0='?'], CommandType='Text', CommandTimeout='30']
      SELECT * FROM author WHERE first_name like '%@p0%'

#this is the LINQ expression query, which works

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__name_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT a.id, a.first_name, a.last_name, a.nationality
      FROM public.author AS a
      WHERE (@__name_0 = '') OR (STRPOS(a.first_name, @__name_0) > 0)

I see that the LINQ/Fluent version transpiles in a rather strange SQL I'm unable to understand, but unfortunately I do not also understand why the code doesn't expand correctly my param in the raw/interpolated SQL.

Thank you for any hint!

Answer 1

OK,

I've got it! And in the while ErikEJ answered in the comments.

TL; DR: basically you need to include the wildcards into the original C# variable

In my original code there were a number of issues:

1. expanding a parameter into a like statement doesn't even work in plain Postgres. Let me explain: when I've tested the raw query with pgAdim, I've written the straight query. Let say the author's name was matteo. I've tested the query as:

SELECT * FROM author WHERE first_name like '%matteo%'

Anyway, any DB connection layer is possibly oriented towards prepared statements rather than raw queries, for that reason I've tried this new code in SQL:

DEALLOCATE foo;
PREPARE foo (text) AS
    SELECT * FROM author WHERE first_name like '%$1%';

execute foo('matteo');

this fails also in SQL!

2. what works in plain SQL as a prepared statement is:

DEALLOCATE foo;
PREPARE foo (text) AS
    SELECT * FROM author WHERE first_name like $1;

execute foo('%matteo%');

So I've tried this code:

public static async Task<Task> getWithName(HttpContext c) {
            var name = c.Request.RouteValues["name"].ToString();
            var name2 = "'%"+name+"%'"; //<- please notice the single quote for SQL strings!

            // with API - WORKS!
            var authors = await DB.Authors.Where(a => a.first_name.Contains(name)).ToListAsync();
            // with raw SQL interpolated - BROKEN

            var authors2 = await DB.Authors.FromSqlInterpolated($"SELECT * FROM author WHERE first_name like {name2}").ToListAsync();
            // with raw SQL parametrized - BROKEN
            var authors3 = await DB.Authors.FromSqlRaw("SELECT * FROM author WHERE first_name like {0}", name2).ToListAsync();
            // with LINQ expressions - WORKS!
            var authors4 = await (from a in DB.Authors where a.first_name.Contains(name) select a).ToListAsync();

            c.Response.ContentType = "application/json";
            return c.Response.WriteAsync(Serialize(authors));
        }

unfortunately this failed again.

3. the reason for my code to fail is the following: EF Core already casts from a C# string to a SQL text type, therefore the single quote is uneeded!!! My code wasc omparable to something like:

DEALLOCATE foo;
PREPARE foo (text) AS
    SELECT * FROM author WHERE first_name like $1;

execute foo(''%matteo%''); //<- double single quote caused by the EF Core automatic cast.

So the solution to the problem has been:

public static async Task<Task> getWithName(HttpContext c) {
            var name = c.Request.RouteValues["name"].ToString();
            var name2 = "%"+name+"%"; //<- please notice: NO MORE single quote!
            // with API - WORKS!
            var authors = await DB.Authors.Where(a => a.first_name.Contains(name)).ToListAsync();
            // with raw SQL interpolated - BROKEN

            var authors2 = await DB.Authors.FromSqlInterpolated($"SELECT * FROM author WHERE first_name like {name2}").ToListAsync();
            // with raw SQL parametrized - BROKEN
            var authors3 = await DB.Authors.FromSqlRaw("SELECT * FROM author WHERE first_name like {0}", name2).ToListAsync();
            // with LINQ expressions - WORKS!
            var authors4 = await (from a in DB.Authors where a.first_name.Contains(name) select a).ToListAsync();

            c.Response.ContentType = "application/json";
            return c.Response.WriteAsync(Serialize(authors));
        }

basically you need to include the wildcards into the original C# variable

NEW Q.: is there no other solution then this ugly wildcard inclusion?!