EF Core 2.2，将字符串参数传递给FromSql语句

本文介绍了EF Core 2.2，将字符串参数传递给FromSql语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

公开列表< 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(); }