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)]
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()
{
}
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)
{
}
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());
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;
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();
Seeder.Seed(Context);
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());
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
- 20th September, 2017: Initial version