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.
1public class SpAsyncEnumerableQueryable<T> : IAsyncEnumerable<T>, IQueryable<T>
2{
3 private IAsyncEnumerable<T> _spItems;
4 public Expression Expression => throw new NotImplementedException();
5 public Type ElementType => throw new NotImplementedException();
6 public IQueryProvider Provider => throw new NotImplementedException();
7
8 public SpAsyncEnumerableQueryable(params T[] spItems)
9 {
10 _spItems = AsyncEnumerable.ToAsyncEnumerable(spItems);
11 }
12
13 public IEnumerator<T> GetEnumerator()
14 {
15 return _spItems.ToEnumerable().GetEnumerator();
16 }
17
18 IEnumerator IEnumerable.GetEnumerator()
19 {
20 return GetEnumerator();
21 }
22
23 IAsyncEnumerator<T> IAsyncEnumerable<T>.GetEnumerator()
24 {
25 return _spItems.GetEnumerator();
26 }
27}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.
1public static class DbSetExtensions
2{
3 public static DbSet<T> MockFromSql<T>(this DbSet<T> dbSet, SpAsyncEnumerableQueryable<T> spItems) where T : class
4 {
5 var queryProviderMock = new Mock<IQueryProvider>();
6 queryProviderMock.Setup(p => p.CreateQuery<T>(It.IsAny<MethodCallExpression>()))
7 .Returns<MethodCallExpression>(x =>
8 {
9 return spItems;
10 });
11
12 var dbSetMock = new Mock<DbSet<T>>();
13 dbSetMock.As<IQueryable<T>>()
14 .SetupGet(q => q.Provider)
15 .Returns(() =>
16 {
17 return queryProviderMock.Object;
18 });
19
20 dbSetMock.As<IQueryable<T>>()
21 .Setup(q => q.Expression)
22 .Returns(Expression.Constant(dbSetMock.Object));
23 return dbSetMock.Object;
24 }
25}Finally, the unit test looks like this.
1[Fact]
2public async Task Get_Results()
3{
4 //Arrange
5 var products = new SpAsyncEnumerableQueryable<Product>(new Product()
6 {
7 ProductId = Guid.NewGuid(),
8 ProductName = "Some name",
9 Size = 1,
10 Value = 2
11 }, new Product()
12 {
13 ProductId = Guid.NewGuid(),
14 ProductName = "Some other name",
15 Size = 3,
16 Value = 4
17 });
18
19 var productContextOptions = new DbContextOptionsBuilder<ProductContext>()
20 .UseInMemoryDatabase(databaseName: "Get results")
21 .Options;
22
23 ProductContext productContext = new ProductContext(productContextOptions);
24
25 productContext.Products = productContext.Products.MockFromSql(products);
26
27 ProductsController controller = new ProductsController(productContext);
28
29 //Act
30 IActionResult actionResult = await controller.Get();
31
32 //Assert
33 OkObjectResult okObjectResult = actionResult as OkObjectResult;
34 Assert.NotNull(okObjectResult);
35 List<Product> productResponse = okObjectResult.Value as List<Product>;
36
37 Assert.Equal(2, productResponse.Count);
38}Full source code available here.