Calling Stored Procedures in EF Core

October 12, 2018    Entity Framework Asp.Net Core .Net

Calling Stored Procedures in EF Core

My co-worker (I helped a little) went through multiple iterations finding the correct way to call a stored procedure using EF Core (2.2, but should apply in higher versions as well).

String Interpolation - DBNull.Value

await _context.Database.ExecuteSqlCommandAsync(
    $"EXEC MySproc @ID={DbNull.Value}", cancellationToken);

I have an optional int column. {"@t":“2020-01-06T15:33:32.6016115Z”,"@mt":“No type was specified for the decimal column ‘{property}’ on entity type ‘{entityType}’. This will cause values to be silently truncated if they do not fit in the default precision and scale. Explicitly specify the SQL server column type that can accommodate all the values using ‘HasColumnType()’.

Changing to Null works fine:

await _context.Database.ExecuteSqlCommandAsync(
    $"EXEC MySproc @ID={null}", cancellationToken);

It is probably more clear to use the more verbose approach.

var parameters = new List<SqlParameter>
    new SqlParameter("@Id", SqlDbType.UniqueIdentifier) { Value = DBNull.Value })

await _context.Database.ExecuteSqlCommandAsync($"EXEC MySproc @Id",

Output Parameter

var requestUidOutputParam = new SqlParameter("@RequestUID", SqlDbType.UniqueIdentifier) { Direction = ParameterDirection.Output };

// EF Core requires that all stored procedure parameters are sent (even defaulted ones) if we want to use output parameters
var priceItems = await _context.PricingItems
    .FromSql("PriceItems @AccountID, @RequestUID OUTPUT, @PriceItems, @ServiceID",
        new SqlParameter("@AccountID", SqlDbType.Int) { Value = DBNull.Value },
        new SqlParameter("@PriceItems", SqlDbType.Structured) { TypeName = "PWB_TT_PriceItems_V4", Value = BuildEmptyPriceItemTableType() },
        new SqlParameter("@ServiceID", serviceId))

if (!Guid.TryParse(requestUidOutputParam.Value?.ToString(), out var requestUid))
    throw new ArgumentException("error converting RequestUID");

No response


Watch the Story for Good News
I gladly accept BTC Lightning Network tips at [email protected]

Please consider using Brave and adding me to your BAT payment ledger. Then you won't have to see ads! (when I get to $100 in Google Ads for a payout, I pledge to turn off ads)

Use Brave

Also check out my Resources Page for referrals that would help me.

Swan logo
Use Swan Bitcoin to onramp with low fees and automatic daily cost averaging and get $10 in BTC when you sign up.