Entity Framework Core Intermediate

Calling stored procedures

EF Core can call stored procedures and raw SQL while keeping parameters safe. This lesson shows FromSql for procedures that return entities, SqlQuery for unmapped results, ExecuteSql for commands, output parameters, and mapping inserts and updates to procedures.

Running SQL from EF Core
  1. FromSqlProcedure or query that returns entities
  2. SqlQuery<T>Any shape of result, including scalars
  3. ExecuteSqlCommands that change data
  4. SqlParameter OutputRead values set by the procedure
  5. UsingStoredProcedureSend SaveChanges through procedures

Procedures that return entities

FromSql runs SQL and materializes entities. Interpolated values become parameters, so this is safe from SQL injection. Because a procedure cannot be composed, call AsEnumerable or ToListAsync right after it.

C#
var minPrice = 500m;
var products = await db.Products
    .FromSql($"EXEC dbo.GetProductsAbovePrice @MinPrice = {minPrice}")
    .AsNoTracking()
    .ToListAsync();

Results that are not entities

SqlQuery maps rows to any class or to a scalar type, without needing a DbSet.

C#
public record SalesByMonth(int Year, int Month, decimal Revenue);

var report = await db.Database
    .SqlQuery<SalesByMonth>($"EXEC dbo.SalesByMonth @Year = {year}")
    .ToListAsync();

var count = await db.Database
    .SqlQuery<int>($"SELECT COUNT(*) AS Value FROM Orders WHERE Status = {status}")
    .SingleAsync();

Commands and output parameters

ExecuteSqlAsync runs a procedure that changes data and returns the number of rows affected. For output parameters, create a SqlParameter with Direction set to Output.

C#
var orderId = new SqlParameter("@OrderId", SqlDbType.Int) { Direction = ParameterDirection.Output };

await db.Database.ExecuteSqlRawAsync(
    "EXEC dbo.PlaceOrder @CustomerId, @ProductId, @Quantity, @OrderId OUTPUT",
    new SqlParameter("@CustomerId", customerId),
    new SqlParameter("@ProductId", productId),
    new SqlParameter("@Quantity", quantity),
    orderId);

int newId = (int)orderId.Value;

Mapping SaveChanges to procedures

  • EF Core 7+ can send inserts, updates and deletes through procedures: InsertUsingStoredProcedure, UpdateUsingStoredProcedure and DeleteUsingStoredProcedure in the Fluent API.
  • This suits databases where the DBA requires all writes to go through procedures.
  • Never pass a concatenated string to FromSqlRaw or ExecuteSqlRaw; use the interpolated versions or SqlParameter objects.

Interview question

What is the difference between FromSql and FromSqlRaw in EF Core?

FromSql takes an interpolated string and turns each interpolated value into a DbParameter, so it is safe from SQL injection. FromSqlRaw takes a plain string plus separate parameters; it is needed for dynamic SQL, but joining user input into that string creates an injection risk. Prefer FromSql and only use the Raw version with explicit parameters.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.