Entity Framework Core, Calling Stored Procedures and Returning to a Model

Full source code available here.

I wrote a post some time back about calling a stored procedure with Entity Framework using the DbCommand, but it was a bit complicated and not that easy to use.

There is now a FromSql method, you just pass the name of the stored procedure and the parameters. The method returns the results and maps them into a model that matches.

First step is to examine what the stored proc returns. I’m using the NorthWind database and its CustOrderHist stored procedure, its output looks like this – I know from the stored proc that this is a string and int.

I created a class to match –

public class OrderHistory
{
    private OrderHistory() { }

    public int Total { get; private set; }
    public string ProductName { get; private set; }
}

In Entity Framework Core 2 you can call the stored proc like this –

List<OrderHistory> orderHistoryList = await _northwindContext.OrderHistory.FromSql($"EXECUTE dbo.CustOrderHist {customerId}").ToListAsync();

In earlier versions you can use this –

List<OrderHistory> orderHistoryList = await _northwindContext.OrderHistory.FromSql("EXECUTE dbo.CustOrderHist {0}", customerId).ToListAsync();

Full source code available here.

Leave a Reply

Your email address will not be published. Required fields are marked *