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 -

1
2
3
4
5
6
7
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 -

Full source code available here.

comments powered by Disqus

Related