EF Core 2.2，将字符串参数传递给FromSql语句 [英] EF Core 2.2, Passing String Parameter to FromSql Statement

问题描述

 公开列表< JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber（）
 {
 return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql（
 $ SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE（Job_Number ='123'） 
）.ToList（）; 
} 
 
对应的SQL：
 
 SELECT * 
 FROM JC_PHASE_MASTER_TOTAL1_MC 
 WHERE 
（Job_Number ='123'）
  

表中的Job_Number字段是一个varchar，并使用前导空格存储未使用的数字。当我使用硬编码的Job_Number运行上述函数时，查询将按预期运行。但是，通过传递与参数相同的值而产生的SQL看起来像这样，并且SQL运行似乎传递数字而不是字符串：

 公共列表< JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber（字符串jobNumber）
 {
 return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql（
 $ SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE（Job_Number = {jobNumber}） 
）.ToList（） 
} 
 
对应的SQL，其中'123'传递给jobNumber字符串参数：
 
 SELECT * 
 FROM JC_PHASE_MASTER_TOTAL1_MC 
 WHERE 
（Job_Number = 123）
  

为什么将字符串参数作为123而不是'传递给SQL语句123'？我不确定如何调整函数，以使参数不会作为数字传递。

每个可接受的解决方案的修订代码：

  public List< JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber（string jobNumber）
 {
 var jobNumberParameter = new SqlParameter（ @ jobNumber，SqlDbType.VarChar）; 
 jobNumberParameter.Value = jobNumber; 
 
 return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql（
 $ SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE（Job_Number = {jobNumberParameter}） 
）.ToList（）; 
} 
  

解决方案

您是否尝试过明确设置SQL数据类型，例如： var jobNumberParameter = new SqlParameter（ @ jobNumber，SqlDbType.VarChar）;
jobNumberParameter.Value = jobNumber;

return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql（
$ SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE（Job_Number = {jobNumberParameter}）
）.ToList（）;
}

public List<JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber()
{
    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = '  123')"
    ).ToList();
}

Corresponding SQL:

SELECT *
FROM JC_PHASE_MASTER_TOTAL1_MC
WHERE
    (Job_Number = '  123')

The Job_Number field in the table is a varchar and is stored with leading spaces for any unused digits. When I run the above function with Job_Number hard-coded, the query runs as intended. However, the SQL produced by passing the same value as a parameter looks like this and the SQL run appears to pass a number rather than a string:

public List<JC_PHASE_MASTER_TOTAL1_MC> GetAllByCompanyCodeJobNumber(string jobNumber)
{
    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = {jobNumber})"
    ).ToList();
}

Corresponding SQL with '   123' passed to the jobNumber string parameter:

SELECT *
FROM JC_PHASE_MASTER_TOTAL1_MC
WHERE
    (Job_Number = 123)

Why is the string parameter passed into the SQL statement as 123 and not ' 123'? I'm unsure how to adjust my function so that the parameter is not passed as a number.

Revised code per accepted solution:

public List<JC_PHASE_MASTER_TOTAL1_MC>GetAllByCompanyCodeJobNumber(string jobNumber)
{
    var jobNumberParameter = new SqlParameter("@jobNumber", SqlDbType.VarChar);
    jobNumberParameter.Value = jobNumber;

    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = {jobNumberParameter})"
    ).ToList();
}

解决方案

Have you tried explicitly setting the SQL data type, something like:

public List<JC_PHASE_MASTER_TOTAL1_MC>GetAllByCompanyCodeJobNumber(string jobNumber)
{
    var jobNumberParameter = new SqlParameter("@jobNumber", SqlDbType.VarChar);
    jobNumberParameter.Value = jobNumber;

    return _context.JC_PHASE_MASTER_TOTAL1_MC.FromSql(
        $"SELECT * FROM JC_PHASE_MASTER_TOTAL1_MC WHERE (Job_Number = {jobNumberParameter})"
    ).ToList();
}

这篇关于EF Core 2.2，将字符串参数传递给FromSql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！