问题:FromSqlInterpolated/FromSqlRaw 中的 LIKE 运算符不起作用,但流畅的 API 或 LINQ 表达式可以

我正在使用 .NET 3.1 上的 EF Core 润湿我的脚。我有以下代码摘录:

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));
        }

除了这个方法缺少上下文之外,让我感到困惑的是,流畅的 API 版本和 LINQ 查询表达式版本都按预期工作,返回数据库中的 12 个项目。相反,插值 SQL 和原始 SQL 都失败返回 0 个项目。

请注意:这不是我得到异常或错误。他们只是返回 0 结果,就好像查询错误一样。

为了进行原始测试,我刚刚放置了一个断点,复制了 name 值并直接在 pgAdmin 中执行了查询。查询按预期工作,返回相同的 12 项。

这是您在代码中看到的 4 个查询的(相当复杂的)调试输出:

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

我看到 LINQ/Fluent 版本以我无法理解的相当奇怪的 SQL 进行转换,但不幸的是,我也不明白为什么代码不能正确扩展我在原始/插值 SQL 中的参数。

谢谢你的任何提示!

解答

好的,

我懂了!同时 ErikEJ 在评论中回答了。

TL; DR:基本上您需要将通配符包含到原始 C# 变量中

在我的原始代码中存在许多问题:

1. 将参数扩展为 like 语句甚至在普通 Postgres 中都不起作用。让我解释一下:当我用 pgAdim 测试原始查询时,我已经编写了直接查询。假设作者的名字是_matteo_。我已将查询测试为:

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

无论如何,任何数据库连接层都可能面向准备好的语句而不是原始查询,因此我在 SQL 中尝试了这个新代码:

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

execute foo('matteo');

这在 SQL 中也失败了!

2. 在普通 SQL 中作为准备好的语句起作用的是:

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

execute foo('%matteo%');

所以我试过这段代码:

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));
        }

不幸的是,这又失败了。

3. 我的代码失败的原因如下: EF Core already casts from a C# string to a SQL text type,因此不需要单引号!!!我的代码类似于:

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.

所以问题的解决方案是:

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));
        }

基本上您需要将通配符包含到原始 C# 变量中

新问题。:除了这个 ugly 通配符包含之外,还有其他解决方案吗?!