Introduction

It is a very old question: "How to test code, that works with database in some way?". There are two approaches: using real database at your tests, but at this case, you will face a lot of problems (creation, performance, interdependencies and so on) or mock corresponding database layer (Repository pattern). For me, the last approach is preferable, but if you do not follow the Repository pattern and use instance of DbContext directly and/or your unit tests logic is enough complicated, so mocking becomes very tedious and complex: for example you don't want to mock unique constraint exception throwing manually, when trying to add already existed item - auto throwing would be much better and desirable. And at this point Effort library, that satisfies all our needs comes to scene.

Effort is a library, that allows you to write unit tests of your code, where you use instance of DbContext directly, without intermediate Repository layer. Effort creates instance of your custom context, providing special connection to its constructor (Read this):

using (var ctx = new PeopleDbContext(Effort.DbConnectionFactory.CreateTransient()))
{
    ctx.People.Add(new Person() { Id = 1, Name = "John Doe" });
    ctx.SaveChanges();
}

As a result, you will make a deal not with real database, but with some kind of in memory database. So you will have several advantages:

You should not create a real database.
Your tests become completely independent, because, obviously, in case of a real database, you will not create a separate database for each test - the one for all of them. But now, it is suitable, simple and good decision to have separate in memory database for each test.
Tests will pass much faster.
If you follow continuous integration paradigm, it will allow you to solve problem with creation or obtaining of real database especially in the cloud.
In memory database does not mean that it is just some kind of "dummy" container of data, instead it is very respectable simulation of real relational database with almost all corresponding features, restrictions and constraints.

So article about: how to implement Effort in practice: how to inject it into your main and test infrastructures, explanation of what to do at case of existence of views and triggers and some other points of interest.

Preconditions and Solution

Let's consider some little demonstration scenario. And, yes I see, that it is bad ER architecture, all of it just for example. We have three tables with POCOs:

public class Stock : BaseModel
{               
    public virtual ICollection<Product> Products { get; set; }               
    public decimal TotalAmount { get; set; }
}

public class Product : BaseModel
{        
    public decimal Price { get; set; }
    [StringLength(32)]
    public string Category { get; set; }   

    public virtual Stock Stock { get; set; }
    public int StockId { get; set; }
}

public class CategoryBalance
{        
    [Key]
    [StringLength(32)]
    public string Category { get; set; }
    public int Quantity { get; set; }
    public decimal Amount { get; set; }
}

public abstract class BaseModel
{
    public int Id { get; set; }        
    [Required]
    [StringLength(32)]
    //[Column(TypeName = "varchar")]
    public string Name { get; set; }
}

Where each product located at specific stock(StockId), has Price and belongs to specific Category. TotalAmount - is sum of all products, which located at specific Stock, worth noting, that this column is calculated and assigned by means of trigger(ProductTrigger), when we perform insert/delete/update operations on products table. CategoryBalance - is not a table, but "aggregation" view based on products table, view's query will look like:

select Category, Sum(Price) Amount, Count(*) Quantity
from Products
group by Category

So T-SQL scripts for view (instead of autogenerated code for table creation) and trigger, probably, will be located at Migration Up via Sql method.

We have a simple service with two routine methods: Add and Delete products:

public class Service
{
    private IDataContextFactory Factory { get; set; }
    public Service(IDataContextFactory factory)
    {
        Factory = factory;
    }

    public int AddProduct(string name, decimal price, string category, int stockId)
    {
        using (var context = Factory.CreateContext())
        {
            var product = context.Products.Add(new Product { 
                Name = name, Category = category, Price = price, StockId = stockId 
            });
            context.SaveChanges();
            return product.Id;
        }
    }

    public void DeleteProduct(int id)
    {
        using (var context = Factory.CreateContext())
        {
            var product = context.Products.Find(id);
            context.Products.Remove(product);
            context.SaveChanges();
        }
    }
}

Factory is needed to inject the manner of DbContext creating:

public interface IDataContextFactory
{
    DataContext CreateContext();
}

public class DataContextFactory : IDataContextFactory
{
    public DataContext CreateContext()
    {
        return new DataContext();
    }
}

So in case of production, we will just create usual instance of context, but in case of testing, it will be created with help of Effort (will be shown later). Now let's consider context class:

public class DataContext : DbContext
{
    public DataContext()
    {
    }

    //this constructor is needed only for Effort, as attachment point
    //we will pass connection, fetched from this library as argument
    public DataContext(DbConnection connection) : base(connection, true)
    {            
    }
        
    public DbSet<Product> Products { get; set; }
    public DbSet<CategoryBalance> CategoryBalances { get; set; }
    public DbSet<Stock> Stocks { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        OnModelCreatingNotCompatibleWithEffort(modelBuilder);
        base.OnModelCreating(modelBuilder);
    }
        
    protected virtual void OnModelCreatingNotCompatibleWithEffort
                      (DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().Property(x => x.Name).HasColumnType("varchar");
        modelBuilder.Entity<Stock>().Property(x => x.Name).HasColumnType("varchar");
    }
}

Here, we have additional constructor only for Effort and OnModelCreatingNotCompatibleWithEffort method. Two classes are inherited from BaseModel, where ColumnAttribute is committed, but essence of it is moved to mentioned method. We did it to show one pitfall of Effort - it not supports some stuff, like this attribute. To fix it, you can declare your intention via Fluent API, move it to separate method and then override it with blank implementation. You can do the same with all, what is not compatible with Effort and has no big matter for testing purposes:

public class EffortContext : DataContext
{
    protected override void OnModelCreatingNotCompatibleWithEffort
                       (DbModelBuilder modelBuilder)
    {
        //blank implementation
    }

    public EffortContext(DbConnection connection) : base(connection)
    {
        MockCategoryBalance();
    }

    public override int SaveChanges()
    {
        MockProductTrigger();
        return base.SaveChanges();
    }        

    private void MockCategoryBalance()
    {
        var view = (from product in Products
                    group product by product.Category into sub
                    select new
                    {
                        sub.Key,
                        Amount = sub.Sum(x => x.Price),
                        Quantity = sub.Count()
                    }).AsEnumerable()
                    .Select(x => new CategoryBalance
                    {
                        Category = x.Key,
                        Amount = x.Amount,
                        Quantity = x.Quantity
                    }).AsQueryable();

        var mockSet = new Mock<DbSet<CategoryBalance>>();

        mockSet.As<IQueryable<CategoryBalance>>().Setup
                  (m => m.Provider).Returns(view.Provider);
        mockSet.As<IQueryable<CategoryBalance>>().Setup
                  (m => m.Expression).Returns(view.Expression);
        mockSet.As<IQueryable<CategoryBalance>>().Setup
                  (m => m.ElementType).Returns(view.ElementType);
        mockSet.As<IQueryable<CategoryBalance>>().Setup(m => m.GetEnumerator())
                                                 .Returns(() => view.GetEnumerator());

        //this would allow to write something like this: 
        //CategoryBalances.Include("SomeRef")
        mockSet.Setup(m => m.Include(It.IsAny<string>())).Returns(() => mockSet.Object);

        CategoryBalances = mockSet.Object;
    }

    private void MockProductTrigger()
    {            
        var changes = ChangeTracker.Entries<Product>().Where
                      (x => x.State != EntityState.Unchanged);
        foreach (var item in changes)
        {
            decimal delta = 0;
            var quantityProperty = item.Property(x => x.Price);
            switch (item.State)
            {
                case EntityState.Deleted:
                    delta = -quantityProperty.CurrentValue;
                    break;
                case EntityState.Added:
                    delta = quantityProperty.CurrentValue;
                    break;
                default:
                    delta = quantityProperty.CurrentValue - 
                            quantityProperty.OriginalValue;
                    break;
            }
            var stock = Stocks.Find(item.Entity.StockId);                
            stock.TotalAmount += delta;
        }                            
    }        
}

Here also presented implementation of CategoryBalances view. We simply write corresponding query(view), just like T-SQL script, shown above and mock CategoryBalances with the help of guidance. Worth noting, that we can't at once write select new CategoryBalance without intermediate anonymous projection and .AsEnumerable() calling, because it is restriction of Entity Framework itself to manually make projection directly to table's class.

ProductTrigger, mentioned above, is implemented and called before SaveChanges method. We analyze changes and perform needed modifications, simulating behavior of real database trigger.

Tests

Now let's consider test infrastructure: another implementation of IDataContextFactory interface:

public class EffortDataContextFactory : IDataContextFactory
{
    private readonly DbConnection Connection;
    //connection we will fetch from Effort stuff
    public EffortDataContextFactory(DbConnection connection)
    {
        Connection = connection;
    }

    public DataContext CreateContext()
    {
        return new EffortContext(Connection);
    }
}

Finally, I will show one test:

[TestClass]
public class UnitTests
{
    private Service Service { get; set; }
    private DataContext Context { get; set; }
    private Stock Stock1 { get; set; }
    private Stock Stock2 { get; set; }       

    [TestInitialize]
    public void TestInitialize()
    {
        var factory = new EffortDataContextFactory
                      (Effort.DbConnectionFactory.CreateTransient());
        Context = factory.CreateContext();

        //it is implementation of standard, 
        //well known Seed method from Configuration class
        Seeder.Seed(Context);
        //Seed body:
        //context.Stocks.AddOrUpdate(x => x.Name, new Stock { Name = "First" });
        //context.Stocks.AddOrUpdate(x => x.Name, new Stock { Name = "Second" });
        //context.SaveChanges();

        Stock1 = Context.Stocks.Where(x => x.Name == "First").Single();
        Stock2 = Context.Stocks.Where(x => x.Name == "Second").Single();
        Service = new Service(factory);            
    }        

    [TestCleanup]
    public void TestCleanup()
    {
        Context.Dispose();
    }             

    [TestMethod]
    public void AddProducts()
    {
        Service.AddProduct("product1", 10, "category1", Stock1.Id);
        Service.AddProduct("product2", 20, "category1", Stock1.Id);
        Service.AddProduct("product3", 30, "category2", Stock1.Id);
        Service.AddProduct("product4", 40, "category2", Stock2.Id);
        Service.AddProduct("product5", 50, "category2", Stock2.Id);

        Assert.AreEqual(150, Context.Products.Sum(x => x.Price));
        Assert.AreEqual(5, Context.Products.Count());

        //to refresh entities
        Context.Entry(Stock1).Reload();
        Context.Entry(Stock2).Reload();

        Assert.AreEqual(60, Stock1.TotalAmount);
        Assert.AreEqual(90, Stock2.TotalAmount);

        var category = Context.CategoryBalances.Single(x => x.Category == "category1");
        Assert.AreEqual(30, category.Amount);
        Assert.AreEqual(2, category.Quantity);

        category = Context.CategoryBalances.Single(x => x.Category == "category2");
        Assert.AreEqual(120, category.Amount);
        Assert.AreEqual(3, category.Quantity);            
    }       
}

Now each test has its own separated Context and Service, where ProductTrigger will work behind the scene and CategoryBalances also will do its work like real database view, so we can rely on their behavior inside and outside Service without any restriction.

Conclusions

In this article, I have shown how to test code, which works with database via Effort. Advantages of this library were presented as well as, how to workaround some Effort's pitfalls like not allowed attributes, how to simulate views and triggers and common approach at all. You can find all code with several tests at this link.

History

20^th September, 2017: Initial version

In Memory Entity Framework Testing via Effort Library, In Practice