Click here to Skip to main content
15,035,723 members
Articles / DevOps / Testing
Posted 19 Sep 2017


4 bookmarked

In memory Entity Framework testing via Effort library, in practice.

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
19 Sep 2017CPOL4 min read
Practical guidance how to use Effort library in your unit tests including workaround with views and triggers and some pitfalls.


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, perfomance, interdependences and so on) or mock corresponding database layer (Repository pattern). For me, last approach is preferable, but if you not follow 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 manualy, 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 it's constructor

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

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

  1. You should not create real database.
  2. Your tests become completely independent, because, obviously, at case of real database, you will not create separate database for each test - the one for all of them. But now it is suitable, simple and good decision to have separete in memory database for each test.
  3. Tests will pass much faster.
  4. If you follow continious integration paradigm, it will allow you to solve problem with creation or obtaining of real database especially in the cloud.
  5. In memory database not means, 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 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; }
    public string Category { get; set; }   

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

public class CategoryBalance
    public string Category { get; set; }
    public int Quantity { get; set; }
    public decimal Amount { get; set; }

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

Where each product located at specific stock(StockId), has Price and belongs to specific CategoryTotalAmount - 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 
            return product.Id;

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

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 at case of production, we will just create usual instance of context, but at 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)
    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 commited, 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 separete 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)

    public override int SaveChanges()
        return base.SaveChanges();

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

        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;
                case EntityState.Added:
                    delta = quantityProperty.CurrentValue;
                    delta = quantityProperty.CurrentValue - quantityProperty.OriginalValue;
            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 anylize changes and perform needed modifications, simulating behavior of real database trigger.


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:

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

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

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

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

    public void TestCleanup()

    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

        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 it's work like real database view, so we can rely on their behavior inside and outside Service without any restriction.


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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Russian Federation Russian Federation
No Biography provided

Comments and Discussions

SuggestionProvide project with source code please Pin
Mou_kol21-Sep-17 3:48
MemberMou_kol21-Sep-17 3:48 
GeneralRe: Provide project with source code please Pin
SlavaUtesinov21-Sep-17 19:30
MemberSlavaUtesinov21-Sep-17 19:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.