sql-server - 使用 string.Format 时，Entity Framework Core 中的原始 SQL 查询无法正常工作

Question

此查询正常工作：

var results = _mp4Db.Videos.FromSql(
            "Select * 
             from Video 
             where isValid = 1 
               and (contains(Subject,'\"" + text + "" + '*' + "\"') 
                    or contains(Description,'\"" + text + "" + '*' + "\"')) 
             order by 
                 case 
                    when contains(Subject,'\"" + text + "" + '*' + "\"') 
                       then 1 
                       else 2 
                 end, 
                 len(Subject) 
             offset 20 rows fetch next 20 rows only ");

但是，如果我使用string.Format更清晰的代码，它不会返回它应该返回的结果：

 var results = _mp4Db.Videos.FromSql(
            "Select * 
             from Video 
             where isValid=1 
                 and (contains(Subject,'\"{0}{1}\"') 
                 or contains(Description,'\"{0}{1}\"')) 
             order by case 
                 when contains(Subject,'\"{0}{1}\"') 
                     then 1 
                     else 2 End , 
                 LEN(Subject) 
             Offset 20 rows fetch next 20 rows only ", text, '*');

知道这里缺少什么吗？

标签： sql-serverentity-framework-corestring.format

Answer 1

尝试使用$运算符并通过调用.ToList()方法执行查询：

var results = _mp4Db.Videos.FromSql(
    $"Select * from Video where isValid=1 and (contains(Subject,'\"{text}{'*'}\"') " +
        $"or contains(Description,'\"{text}{'*'}\"')) " +
        $"order by case when contains(Subject,'\"{text}{'*'}\"') then 1 " +
        $"else 2 End , LEN(Subject) Offset 20 rows fetch next 20 rows only "
).ToList();