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 created a class to match -

1public class OrderHistory
2{
3    private OrderHistory() { }
4
5    public int Total { get; private set; }
6    public string ProductName { get; private set; }
7}

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 -

Full source code available here.