表中的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语句
发布评论