使用EntityFrameworkCore 2的.FromSql调用MSSQL存储过程的技术问询

Hey there! Let's break down your Entity Framework Core 2.x code for calling a SQL Server stored procedure, and cover common technical questions, fixes, and optimizations you might care about:

First, let's finish the truncated code you shared (since it cuts off mid-implementation) to make it functional:

public IList<Trader> GetTradersWithinRadius(int category, decimal latitude, decimal longitude) {
    var sproc = "FindTradersWithinRadiusLatLong";
    var sqlParams = new List<SqlParameter>() {
        new SqlParameter("@CATEGORY", category),
        new SqlParameter("@LAT", latitude),
        new SqlParameter("@LONG", longitude),
    };
    var traders = this.Traders.FromSql($"{sproc} @CATEGORY, @LAT, @LONG", sqlParams.ToArray())
                             .ToList();
    return traders;
}

This code works by:

1. Defining the stored procedure name
2. Creating strongly-typed SqlParameter objects (critical for avoiding SQL injection)
3. Using EF Core's FromSql method to execute the procedure and map results directly to your Trader entity
4. Converting the query result to a list to return

Let's tackle the most frequent issues developers run into with this pattern:

1. What if the stored procedure's output columns don't match my Trader entity properties? #

EF Core requires exact column name matches between the stored procedure's result set and your entity's properties (case-insensitive in SQL Server, but best to align exactly). If they don't match, you have two options:

• Update the stored procedure to rename output columns to match your entity
• Use the [Column] attribute on your entity properties to map to the stored procedure's column names:

  public class Trader {
      public int Id { get; set; }
      [Column("TraderFullName")] // Maps to a column named TraderFullName in the proc's output
      public string FullName { get; set; }
      // Other properties...
  }
  

2. How do I handle output parameters or return values from the stored procedure? #

Your current code only handles the result set. If your proc has output parameters or a return value, define SqlParameter objects with explicit directions:

// Add an output parameter to track total matching traders
var totalCountParam = new SqlParameter("@TotalTraders", SqlDbType.Int) { 
    Direction = ParameterDirection.Output 
};
sqlParams.Add(totalCountParam);

// Execute the query as before
var traders = this.Traders.FromSql($"{sproc} @CATEGORY, @LAT, @LONG, @TotalTraders OUTPUT", sqlParams.ToArray())
                         .ToList();

// Retrieve the output value after execution
int totalTraders = (int)totalCountParam.Value;

If you only need return values/output parameters (no result set), use Database.ExecuteSqlCommand instead of FromSql.

3. What limitations should I know about FromSql in EF Core 2.x? #

• Limited Linq chaining: You can't add complex Linq operations (like Where or OrderBy) directly after FromSql in EF Core 2.x. Either handle filtering/sorting inside the stored procedure, or call .ToList() first to materialize results before applying Linq logic.
• No multi-result set support: EF Core 2.x can't map multiple result sets from a single stored procedure. If your proc returns multiple sets, you'll need to use a raw SqlDataReader to read them manually.

4. Is this code safe from SQL injection? #

Yes! By using SqlParameter instead of directly concatenating values into the SQL string, you're using parameterized queries, which eliminate SQL injection risks. Never hardcode user input into the procedure call string—your current approach is the correct, secure way.

• Simplify parameter passing: You don't need to convert the list to an object[] explicitly; FromSql accepts params object[], so you can pass the parameters directly:

  var traders = this.Traders.FromSql($"{sproc} @CATEGORY, @LAT, @LONG",
      new SqlParameter("@CATEGORY", category),
      new SqlParameter("@LAT", latitude),
      new SqlParameter("@LONG", longitude))
                           .ToList();
  

• Add error handling: Wrap the query in a try/catch block to handle database-specific exceptions (like connection issues or proc errors):

  try {
      // Query execution code
  } catch (SqlException ex) {
      // Log the error and rethrow with a user-friendly message
      throw new InvalidOperationException("Failed to load nearby traders", ex);
  }
  

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