ASP.NET Web API控制器中如何调用存储过程查询数据

完全对齐你当前的Repository+Service+AutoMapper分层逻辑，不需要调整现有项目的基础配置，实现步骤如下：

• 第一步：定义存储过程对应的强类型模型
  新建和存储过程返回字段一一对应的C#类，不需要在CentralContext中给这个类配置DbSet属性，类的属性名、数据类型必须和存储过程返回的列名、类型完全匹配，示例：

  // 类名根据你的存储过程业务含义自定义即可
  public class SpTransQueryResult
  {
      public long TransId { get; set; }
      public string TransNo { get; set; }
      public decimal Amount { get; set; }
      public DateTime CreateTime { get; set; }
      // 其他字段和存储过程返回列一一对应
  }
  

  注意：如果使用EF Core 3.0+版本，需要在CentralContext的OnModelCreating方法中给该类配置无键实体，否则运行会报主键缺失错误，配置代码：modelBuilder.Entity<SpTransQueryResult>().HasNoKey();

• 第二步：Repository层新增存储过程查询方法
  和你现有Trans相关Repository的写法保持一致，在ITransRepository接口中定义查询方法，在实现类中通过SqlParameter传参调用存储过程，不要直接拼接SQL字符串，示例代码：

  // 接口定义
  Task<List<SpTransQueryResult>> QueryTransBySpAsync(DateTime? startTime, DateTime? endTime, int? transStatus);
  
  // 实现类代码
  public async Task<List<SpTransQueryResult>> QueryTransBySpAsync(DateTime? startTime, DateTime? endTime, int? transStatus)
  {
      var sqlParams = new SqlParameter[]
      {
          new SqlParameter("@StartTime", startTime ?? DBNull.Value),
          new SqlParameter("@EndTime", endTime ?? DBNull.Value),
          new SqlParameter("@TransStatus", transStatus ?? DBNull.Value)
      };
  
      return await _centralContext.Set<SpTransQueryResult>()
          .AsNoTracking()
          .FromSqlRaw("EXEC 你的存储过程名称 @StartTime, @EndTime, @TransStatus", sqlParams)
          .ToListAsync();
  }
  

  这里是之前写法出错概率最高的点：参数值为null时必须传DBNull.Value，不能直接传C# null，否则会触发参数数量不匹配的报错；必须用Set<无键实体类型>来做查询映射，不要直接用Database.ExecuteSqlRaw，后者只会返回影响行数，不会返回查询结果。
• 第三步：Service层对齐现有业务逻辑
  和你现有Trans服务的写法一致，在ITransService中定义对应业务方法，实现类中调用Repository层方法，如果需要做返回字段的裁剪、格式转换，直接注入现有IMapper做对象映射，和你现有GET全量查询接口的逻辑完全一致，不需要额外处理。如果需要返回前端专用DTO，在AutoMapper的Profile中加对应映射规则即可，和现有TRANS实体转DTO的配置方式完全相同。
• 第四步：Controller层新增GET接口
  路由规则、返回格式完全对齐你现有TransController的接口写法，示例：

  [HttpGet("sp-query")]
  public async Task<IActionResult> GetTransBySp([FromQuery] DateTime? startTime, [FromQuery] DateTime? endTime, [FromQuery] int? transStatus)
  {
      var data = await _transService.QueryTransBySpAsync(startTime, endTime, transStatus);
      // 返回结构和你现有GET接口的统一返回格式保持一致
      return Ok(new { code = 200, msg = "查询成功", data });
  }
  

常见运行异常排查点 #

• 检查存储过程名称、参数名和代码中写的是否完全匹配，SQL Server参数名不区分大小写，但字符必须完全对应
• 检查存储过程是否只返回一个结果集，不要在存储过程中写额外的SELECT语句输出多余内容，EF Core只会读取第一个返回的结果集
• 检查强类型模型的属性类型是否和数据库返回的列类型兼容，比如数据库返回的是varchar不要对应成int类型，否则会抛映射错误
• 如果项目用的是EF6（非EF Core），把FromSqlRaw的写法替换成_centralContext.Database.SqlQuery<SpTransQueryResult>(sql, sqlParams).ToListAsync()即可，分层逻辑完全不变。

内容的提问来源于stack exchange，提问作者Hackerman