Unit Test Entity Framework

Tested with:
.Net Framework 4.5
Entity Framework 6.2.0
Moq 4.13.1
MSTest 1.3.2

Github repository here.

This is a demo of how to write unit tests for Entity Framework.
This demo is based on Microsoft article.
It consists of testing EF query and non-query scenarios on both async and non-async calls.

The entity model looks like this.

2019-12-23 14_20_09-Infinity - Microsoft Visual Studio

The data access class look like this.

public class InfinityDataAccess
{
    private readonly InfinityEntities dbContext;
    public InfinityDataAccess(InfinityEntities dbContext)
    {
        this.dbContext = dbContext;
    }

    public IEnumerable<Misc> GetMisc()
    {
        using (this.dbContext)
        {
            return this.dbContext.Misc
                .Select(o => o)
                .ToList();
        }
    }

    public async Task<IEnumerable<Misc>> GetMiscAsync()
    {
        using (this.dbContext)
        {
            return await this.dbContext.Misc
                .Select(o => o)
                .ToListAsync();
        }
    }

    public bool InsertMisc(string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var newModel = new Misc
                {
                    Data = data,
                    Description = description
                };

                this.dbContext.Misc.Add(newModel);
                this.dbContext.SaveChanges();
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> InsertMiscAsync(string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var newModel = new Misc
                {
                    Data = data,
                    Description = description
                };

                this.dbContext.Misc.Add(newModel);
                await this.dbContext.SaveChangesAsync();
                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool UpdateMisc(int miscId, string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = this.dbContext.Misc.Find(miscId);

                if (dataModel != null)
                {
                    dataModel.Data = data;
                    dataModel.Description = description;
                    this.dbContext.SaveChanges();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> UpdateMiscAsync(int miscId, string data, string description)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = await this.dbContext.Misc.FindAsync(miscId);

                if (dataModel != null)
                {
                    dataModel.Data = data;
                    dataModel.Description = description;
                    await this.dbContext.SaveChangesAsync();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public bool DeleteMisc(int miscId)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = this.dbContext.Misc.Find(miscId);

                if (dataModel != null)
                {
                    this.dbContext.Misc.Remove(dataModel);
                    this.dbContext.SaveChanges();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }

    public async Task<bool> DeleteMiscAsync(int miscId)
    {
        try
        {
            using (this.dbContext)
            {
                var dataModel = await this.dbContext.Misc.FindAsync(miscId);

                if (dataModel != null)
                {
                    this.dbContext.Misc.Remove(dataModel);
                    await this.dbContext.SaveChangesAsync();
                }

                return true;
            }
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

For query scenario on non-async call, it is pretty straight forward.

[TestMethod]
public void TestGetMisc()
{
    // Data to be returned
    var data = new List();
    data.Add(new Misc { Data = "Data1", Description = "Description1" });
    data.Add(new Misc { Data = "Data2", Description = "Description2" });
    data.Add(new Misc { Data = "Data3", Description = "Description3" });

    // Create mock data
    var mockData = new Mock();
    mockData.As().Setup(m => m.Provider).Returns(data.AsQueryable().Provider);
    mockData.As().Setup(m => m.Expression).Returns(data.AsQueryable().Expression);
    mockData.As().Setup(m => m.ElementType).Returns(data.AsQueryable().ElementType);
    mockData.As().Setup(m => m.GetEnumerator()).Returns(data.AsQueryable().GetEnumerator());

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(mockData.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = dataAccess.GetMisc();
    Assert.IsNotNull(result);
    Assert.AreEqual(3, result.Count());
    Assert.AreEqual("Description1", result.FirstOrDefault(o => o.Data == "Data1").Description);
}

The test for query scenario creates mock data of queried EF entity model and setup the mock’s Provider, Expression, ElementType and Enumerator. The return object of the setup are the correspondent properties of the returned data which is a type of IQueryable (see // Create mock data in the code above).
Next, the test creates mock of EF context. The mock is setup to return mock data we created earlier (see // Create mock EF context in the code above).

For non-query scenario on non-async call, it is almost similar to query scenario.

[TestMethod]
public void TestInsertMisc()
{
    // Data to be returned
    var data = new Mock();

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(data.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = dataAccess.InsertMisc("UnitTestData1", "UnitTestDescription1");
    Assert.IsTrue(result);
    mockContext.Verify(m => m.Misc.Add(It.IsAny()), Times.Once);
    mockContext.Verify(m => m.SaveChanges(), Times.Once);
}

The test creates mock data of EF entity model, but it is not necessary to setup the mock’s Provider, Expression, ElementType or Enumerator.
Next, the test creates mock of EF context (see // Create mock EF context in the code above).

For async calls, there are additional helper classes we need to create.
These classes are in-memory query provider to process async requests. We use custom query provider because EF use LINQ extension methods for async calls and these methods are designed to be used only with EF. If we try to use them with our unit tests, it will throw errors.
The 3 classes we need to create are:

  1. DbAsyncQueryProvider
  2. DbAsyncEnumerable
  3. DbAsyncEnumerator

With these helper classes, we can create mock data capable of in-memory async calls. This is the generic method to create the mock data.

private Mock GenerateMockDbSet(IQueryable mockData)
    where T : class
{
    var mock = new Mock();
    mock.As()
        .Setup(x => x.GetAsyncEnumerator())
        .Returns(new TestDbAsyncEnumerator(mockData.GetEnumerator()));

    mock.As()
        .Setup(x => x.Provider)
        .Returns(new TestDbAsyncQueryProvider(mockData.Provider));

    mock.As()
        .Setup(x => x.Expression)
        .Returns(mockData.Expression);

    mock.As()
        .Setup(x => x.ElementType)
        .Returns(mockData.ElementType);

    mock.As()
        .Setup(x => x.ElementType)
        .Returns(mockData.ElementType);

    return mock;
}

So for query scenario on async call, we just call the above method to create mock data for us.

[TestMethod]
public async Task TestGetMiscAsync()
{
    // Data to be returned
    var data = new List();
    data.Add(new Misc { Data = "Data1", Description = "Description1" });
    data.Add(new Misc { Data = "Data2", Description = "Description2" });
    data.Add(new Misc { Data = "Data3", Description = "Description3" });

    // Create mock data
    var mockData = GenerateMockDbSet(data.AsQueryable());

    // Create mock EF context
    var mockContext = new Mock();
    mockContext
        .Setup(mock => mock.Misc)
        .Returns(mockData.Object);

    // Assert
    var dataAccess = new InfinityDataAccess(mockContext.Object);
    var result = await dataAccess.GetMiscAsync();
    Assert.IsNotNull(result);
    Assert.AreEqual(3, result.Count());
    Assert.AreEqual("Description1", result.FirstOrDefault(o => o.Data == "Data1").Description);
}

There are more sample tests on the github repository to demonstrate every single scenarios in both async and non-async calls.

Entity Framework, .Net and SQL Server Table Valued Parameter

This is step by step setup of using SQL Server TVP (Table Valued Parameter) in .Net application with EF (Entity Framework). In this example, I use SQL Server 2016 (SP2-CU3), .Net 4.5.1 and EF 6.20.

1. Create a table to store data.

CREATE TABLE [dbo].[Something] (
    [Id]            INT	IDENTITY(1,1)   NOT NULL,
    [Name]          VARCHAR(150)        NOT NULL,
    [Status]        CHAR(1)             NOT NULL,
    CONSTRAINT [PK_Something_Id] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

2. Create `User Defined Table Type` in SQL Server. For simplicity, in this example the type’s columns are same as table I created on step 1. In real-world, the type’s columns could be significantly different than table where we store the data, it might even used for join with other tables.

CREATE TYPE [dbo].[udt_Something] AS TABLE (
	[Name]		VARCHAR(150)	NOT NULL,
	[Status]	CHAR(1)		NOT NULL,
);
GO

3. Create stored procedure to take parameter (of a `User Defined Table` type we created earlier) and perform necessary task to persist our data.

CREATE PROCEDURE [dbo].[sp_SaveSomething]
	@udt_Something [dbo].[udt_Something] READONLY
AS
BEGIN
    INSERT INTO [dbo].[Something]
        SELECT *
        FROM @udt_Something
END
GO

4. Create extension method to convert `IEnumerable<T>` object to a `DataTable` object. In order to use SQL TVP, we have to pass our parameter as a `DataTable`. This method will help convert our data to `DataTable` type.
Helper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace QC
{
    public static class Helper
    {
        public static DataTable ToDataTable<T>(this IEnumerable<T> enumerable, IEnumerable<string> orderedColumnNames)
        {
            var dataTable = new DataTable();

            // Get all properties of the object
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();

            // Get column
            var columnNames = (orderedColumnNames ?? readableProperties.Select(s => s.Name)).ToArray();

            // Add columns to data table
            foreach (string name in columnNames)
            {
                dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);
            }

            // Add rows to data table from object
            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj)).ToArray());
            }

            return dataTable;
        }
    }
}

5. For the purpose of this example, let’s say we want to save a collection of objects. This is our object definition.
Something.cs

namespace QC
{
    public class Something
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Status { get; set; }
    }
}

6. Using EF, called stored procedure we created and pass in `SqlParameter`, which is a collection of objects that we converted to `DataTable`. Don’t forget to specify parameter type as `User Defined Table Type`.
DataAccess.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class DataAccess
    {
        public void Save(IEnumerable<Something> data)
        {
            // Columns for ordering, the order of the columns must be the same as user defined table type
            var orderedCols = new[] { "Name", "Status" };

            // SQL parameter to pass to stored procedure
            var param = new SqlParameter("@udt_Something", SqlDbType.Structured);
            param.Value = data.ToDataTable(orderedCols);
            param.TypeName = "dbo.udt_Something";

            try
            {
                // QCDB is our EF entities
                using (var db = new QCDB())
                {
                    // Call stored procedure and pass in table valued parameter
                    db.Database.ExecuteSqlCommand("EXEC dbo.sp_SaveSomething @udt_Something", param);
                }
            }
            catch
            {
                throw;
            }
        }
    }
}

7. Example of usage.
OpsController.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace QC
{
    public class OpsController : ApiController
    {
        public void SaveSomething()
        {
            var data = new List<Something>();
            data.Add(new Something { Id = 1, Name = "Chap", Status = "A" });
            data.Add(new Something { Id = 2, Name = "Stick", Status = "D" });

            var dataAccess = new DataAccess();
            dataAccess.Save(data);
        }
    }
}