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.

Unit testing Entity Framework Core Stored Procedures

Full source code available here.

Entity Framework Core has made unit testing CRUD functions much easier, see here for an example of using the In Memory Database, it allows you to search, add, remove and update rows.

But the in memory database doesn’t support execution of stored procedures in any way. I’ve seen people suggest that execution of a stored procedure is an integration test, but I don’t agree with that. If testing a method that does CRUD is a unit test, then testing a method that calls a stored procedure must also be unit a test.

An anonymous colleague of mine came up with this technique.

The first thing to do is add a class that implements IAsyncEnumerable and IQueryable.

public class SpAsyncEnumerableQueryable<T> : IAsyncEnumerable<T>, IQueryable<T>
{
	private IAsyncEnumerable<T> _spItems;
	public Expression Expression => throw new NotImplementedException();
	public Type ElementType => throw new NotImplementedException();
	public IQueryProvider Provider => throw new NotImplementedException();

	public SpAsyncEnumerableQueryable(params T[] spItems)
	{
		_spItems = AsyncEnumerable.ToAsyncEnumerable(spItems);
	}

	public IEnumerator<T> GetEnumerator()
	{
		return _spItems.ToEnumerable().GetEnumerator();
	}

	IEnumerator IEnumerable.GetEnumerator()
	{
		return GetEnumerator();
	}

	IAsyncEnumerator<T> IAsyncEnumerable<T>.GetEnumerator()
	{
		return _spItems.GetEnumerator();
	}
}

Then add an extension class for DbSet, it’s DbSet because it has the method FromSql that calls stored procedures. MockFromSql takes a SpAsyncEnumerableQueryable of items that the stored proc will return.

public static class DbSetExtensions
{
	public static DbSet<T> MockFromSql<T>(this DbSet<T> dbSet, SpAsyncEnumerableQueryable<T> spItems) where T : class
	{
		var queryProviderMock = new Mock<IQueryProvider>();
		queryProviderMock.Setup(p => p.CreateQuery<T>(It.IsAny<MethodCallExpression>()))
			.Returns<MethodCallExpression>(x =>
			{
				return spItems;
			});

		var dbSetMock = new Mock<DbSet<T>>();
		dbSetMock.As<IQueryable<T>>()
			.SetupGet(q => q.Provider)
			.Returns(() =>
			{
				return queryProviderMock.Object;
			});

		dbSetMock.As<IQueryable<T>>()
			.Setup(q => q.Expression)
			.Returns(Expression.Constant(dbSetMock.Object));
		return dbSetMock.Object;
	}
}

Finally, the unit test looks like this.

[Fact]
public async Task Get_Results()
{
	//Arrange 
	var products = new SpAsyncEnumerableQueryable<Product>(new Product()
	{
		ProductId = Guid.NewGuid(),
		ProductName = "Some name",
		Size = 1,
		Value = 2
	}, new Product()
	{
		ProductId = Guid.NewGuid(),
		ProductName = "Some other name",
		Size = 3,
		Value = 4
	});

	var productContextOptions = new DbContextOptionsBuilder<ProductContext>()
		.UseInMemoryDatabase(databaseName: "Get results")
		.Options;

	ProductContext productContext = new ProductContext(productContextOptions);

	productContext.Products = productContext.Products.MockFromSql(products);

	ProductsController controller = new ProductsController(productContext);

	//Act
	IActionResult actionResult = await controller.Get();

	//Assert
	OkObjectResult okObjectResult = actionResult as OkObjectResult;
	Assert.NotNull(okObjectResult);
	List<Product> productResponse = okObjectResult.Value as List<Product>;

	Assert.Equal(2, productResponse.Count);
}

Full source code available here.