使用字符串时，实体框架核心中的原始 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, '*');

知道这里缺少什么吗？

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