NET EF sql 参数化查询

导入命名空间

using System.Data.SqlClient;

代码

var sqlBuilder = new StringBuilder("SELECT SUM(TOTAL) AS TOTAL FROM (");
sqlBuilder.Append(" SELECT COUNT(1) AS TOTAL FROM TABLE_A WHERE Code = @originCode UNION ALL");
sqlBuilder.Append(" SELECT COUNT(1) AS TOTAL FROM TABLE_C WHERE Code = @originCode");
sqlBuilder.Append(" ) V");
SqlParameter[] parameters = {
    new SqlParameter("@originCode", originCode ?? string.Empty),
};
var total = db.Database.SqlQuery<int>(sqlBuilder.ToString(), parameters).FirstOrDefault();

PS:

参数需要进行非空判断，把 null 替换为 string.Empty，否则会异常

System.Data.SqlClient.SqlException:“The parameterized query '(@originCode nvarchar(4000))SELECT SUM(TOTAL) AS TOTAL FROM ( SE' expects the parameter '@originCode', which was not supplied.”