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>>();
			.SetupGet(q => q.Provider)
			.Returns(() =>
				return queryProviderMock.Object;

			.Setup(q => q.Expression)
		return dbSetMock.Object;

Finally, the unit test looks like this.

public async Task Get_Results()
	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")

	ProductContext productContext = new ProductContext(productContextOptions);

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

	ProductsController controller = new ProductsController(productContext);

	IActionResult actionResult = await controller.Get();

	OkObjectResult okObjectResult = actionResult as OkObjectResult;
	List<Product> productResponse = okObjectResult.Value as List<Product>;

	Assert.Equal(2, productResponse.Count);

Full source code available here.

14 thoughts on “Unit testing Entity Framework Core Stored Procedures

  1. Hey, Thank you for the code. I couldn’t achieve to make it work. First, it gives throw new NotImplementedException(); because we don’t implement these properties or I may do something wrong, not sure. However, I tried many ways such as set properties outside too, I couldn’t make it work.

    Could you please give me some details how to set them – provider,expression, type-

    Thank you in advance!

  2. Hey,
    Finally I figured out this way :

    public Expression Expression => _spItems.ToEnumerable().AsQueryable().Expression;

    public Type ElementType => typeof(T);

    public IQueryProvider Provider => _spItems.ToEnumerable().AsQueryable().Provider;

  3. Where are you actually testing the stored procedure? As far as this test can tell, you’re just returning the entire list of products through your controller.

    • Ah, good point! I am unit testing methods that use stored procedures by “mocking” the stored proc call and returned data.

      The title of the post is a little off.

  4. This is fantastic, thank you, thank you!

    For future people I had to change this in SpAsyncEnumerableQueryable though:

    IAsyncEnumerator IAsyncEnumerable.GetAsyncEnumerator(CancellationToken cancellationToken)
    return _spItems.GetAsyncEnumerator();

Leave a Reply

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