FromSqlInterpolated/FromSqlRaw中的LIKE运算符不起作用，但Fluent API或LINQ表达式起作用

xyhw6mcr 于 2022-12-06 发布在其他

关注(0)|答案(1)|浏览(161)

我正在为. 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));
        }

除了缺少这个方法的上下文之外，让我困惑的是Fluent API版本和LINQ查询表达式版本都能按预期工作，在DB中返回12个条目，相反，插值和原始SQL都无法返回0个条目。
请注意：这并不是因为我得到了一个异常或任何错误。2他们只是返回0个结果，就好像查询是错误的。
为了进行原始测试，我设置了一个断点，复制了 * name * 值，并直接在pgAdmin中执行查询。
这是您在代码中看到的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中正确扩展我的参数。
谢谢你的提示!

linq

来源：https://stackoverflow.com/questions/62243324/like-operator-in-fromsqlinterpolated-fromsqlraw-not-working-but-fluent-api-or-l

1条答案

按热度按时间

dm7nw8vv1#

好的，
我知道了!而在这同时ErikEJ在评论中回答了。
TL; DR：基本上，您需要将通配符包括到原始C#变量中
在我的原始代码中有许多问题：
1.将参数扩展为like语句在普通Postgres中甚至不起作用。让我来解释一下：当我用pgAdim测试原始查询时，我编写了直接查询，假设作者的名字是 * matteo *，我测试了如下查询：

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

无论如何，任何DB连接层都可能面向预处理语句而不是原始查询，因此我在SQL中尝试了以下新代码：

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

execute foo('matteo');

这在SQL中也会失败!
1.在普通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));
        }

不幸的是，这又失败了。
1.我的代码失败的原因如下：EF Core * 已经将 * 从C#字符串转换为SQL文本类型，因此不需要单引号!!!我的代码类似于：

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#变量中包含通配符**
- 新问题**：除了这个 * 丑陋 * 通配符包含之外，没有其他解决方案了吗？!

赞(0）回复(0）举报 2022-12-06

我来回答

FromSqlInterpolated/FromSqlRaw中的LIKE运算符不起作用，但Fluent API或LINQ表达式起作用

1条答案

相关问题

热门标签

最新问答