Introduction
This tip is a guide for using the Codeplex project Effort for testing Entity Framework applications without requiring the unit tests to actually hit the SQL Server database. Effort instead allows you to create a fake or mock database so that you can focus on testing your Business Logic Layer without having to be concerned about the Data Access Layer. Also, you may find cases where running unit tests against the full-blown SQL Server database simply takes too long to complete, so running these unit tests after a code check-in on your continuous integration server can provide you with more immediate feedback on your code changes.
Effort is an ADO.NET provider tool that allows you to create automated tests, which executes data operations as a light-weight process in memory instead of against the SQL Server database.
Effort stands for the Entity Framework Fake ObjectContext Realization Tool.
My plan for this tip is to start with an Entity Framework project that has unit tests working with a SQL Server database, and then discuss the steps required to get these same unit tests running against the Effort fake in-memory database.
Many thanks to David Bywaters for his excellent article that gave me the inspiration to write this article. Here I will simply focus on Effort instead of also adding the option to run unit tests against a lightweight SQL CE database.
Please note that you can download the source code for Parts 1 and 2 here and for Part 3 here
Part 1: Effort Basics
Setting Up the Database
Download and install SQL Server Express with Advanced Services from MSDN.
Download and install the appropriate version of the AdventureWorks database from Codeplex.
Add this view to the database and call it vProductProductInventory:
SELECT Production.Product.ProductID, Production.Product.Name, _
SUM(Production.ProductInventory.Quantity) AS InventoryCount,
'Color = ' + Production.Product.Color + ', _
Size = ' + Production.Product.Size + ', Weight = ' + _
CAST(Production.Product.Weight AS NVARCHAR) + ', _
Style = ' + Production.Product.Style AS Description
FROM Production.Product LEFT OUTER JOIN
Production.ProductInventory _
ON Production.Product.ProductID = Production.ProductInventory.ProductID
GROUP BY Production.Product.Name, Production.Product.Color, _
Production.Product.Size, Production.Product.Weight, _
Production.Product.Style, Production.Product.ProductID
Adding LINQ to Entities
It is possible to have many different types of data layers, including LINQ-to-SQL, CLR object, a Web service, or LINQ-to-Entities. We are going to use the latter.
Create a new Class Library project and call it Effort.DataAccess. Then choose to add new item. In the dialog that is displayed, choose the ADO.NET Entity Data Model:
Then select "EF Designer from database:"
Next, we need to create a connection to our database, so type "ConnectionString" in the textbox, and click on the new connection button.
In the following dialog, select your SQL Server machine name, and choose the AdventureWorks database, using Windows Authentication:
After this, you need to check all tables, views and stored procedures. Also, ensure that "Include foreign key columns in the model" is checked, and then click Finish:
You should then see a *.edmx file in your solution, with a display showing the tables you selected:
Now, we need to add the EntityFramework.dll reference the Effort.DataAccess project by browsing to this directory to select the file: ~\WcfEntitiesSample\packages\EntityFramework.6.1.2\lib\net45\EntityFramework.dll.
Next add a ProductManager (also known as a repository) class to the AdventureWorksEntityFramework.Effort.BusinessLogic Class Library and add required references:
using System.Collections.Generic;
using System.Linq;
using WcfEntitiesSample.DataAccess;
namespace WcfEntitiesSample.Model
{
public class ProductManager
{
public Product GetProductById(int productID)
{
IEnumerable<Product> products = null;
var db = new AdventureWorksEntities();
products = db.vProductProductInventories
.Where(prod => prod.ProductID == productID)
.Select(p => new Product
{
ProductID = p.ProductID,
Name = p.Name
});
return products.ToList()[0];
}
Next, in your AdventureWorksEntityFramework.Effort.BusinessLogicTest project, ensure you put the connection string into the app.config file:
Adding Unit Test Project
Add a test project to your solution:
You can then install Effort by executing the following command in the package manager console:
PM> Install-Package Effort.EF6
Next, add IAdventureWorksDbContext.cs to your data project. This allows you as the developer to make use of Inversion of Control (IoC) which allows you to pass in different objects into a constructor based on your requirements. For example, if you are in a production mode, you of course would pass in your actual DbContext to connect to your database, while if you are in testing mode, you can pass in a DbContext that allows you to test against a fake database in memory. For full details on IoC, please see details here.
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
namespace WcfEntitiesSample.DataAccess
{
public interface IAdventureWorksDbContext : IDisposable
{
DbContextConfiguration Configuration { get; }
Database Database { get; }
DbSet<vProductProductInventory> vProductProductInventories { get; set; }
int SaveChanges();
}
}
Now update the DbContext for AdventureWorks in AdventureWorksEntities.Context.cs to add a couple parameterized constructors that are needed by Effort:
namespace WcfEntitiesSample.DataAccess
{
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
public partial class AdventureWorksEntities : DbContext, IAdventureWorksDbContext
{
public AdventureWorksEntities()
: base("name=AdventureWorksEntities")
{
}
public AdventureWorksEntities(string nameOrConnectionString)
: base(nameOrConnectionString)
{
this.Configuration.LazyLoadingEnabled = false;
}
public AdventureWorksEntities(DbConnection connection)
: base(connection, true)
{
this.Configuration.LazyLoadingEnabled = false;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public DbSet<vProductProductInventory> vProductProductInventories { get; set; }
}
}
Now update the product manager:
using AdventureWorksEntityFramework.DataAccess;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
namespace AdventureWorksEntityFramework.BusinessLogic
{
public class ProductManager
{
private IAdventureWorksDbContext _dbContext;
public ProductManager(IAdventureWorksDbContext dbContext)
{
dbContext.Configuration.AutoDetectChangesEnabled = false;
this._dbContext = dbContext;
}
public List<Product> GetProducts()
{
IEnumerable<Product> products = null;
products = (from p in _dbContext.vProductProductInventories
select new { ProductID = p.ProductID, Name = p.Name }).ToList()
.Select(x => new Product { ProductID = x.ProductID, Name = x.Name });
return products.ToList();
}
}
}
You also need to set a key for Effort, but we don't want to have to keep re-adding it to the vProductProductInventory.cs file since that file is always auto-generated, so assign the key using a partial class with meta data:
namespace AdventureWorksEntityFramework.DataAccess
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
[MetadataType(typeof(vProductProductInventory_Meta))]
public partial class vProductProductInventory
{
}
public class vProductProductInventory_Meta
{
[Key]
public string ProductID { get; set; }
}
}
Now in the unit test project, add a reference to the data access project and an ITestDatabase interface:
using System;
using WcfEntitiesSample.Data;
namespace WcfEntitiesSample.DataAccessTest
{
public interface ITestDatabase : IDisposable
{
IAdventureWorksDbContext CreateContext();
void Dispose(IAdventureWorksDbContext context);
}
}
Next add a test data class to the unit test project with some fake data for Effort to use:
using System;
using System.Data.Entity.Migrations;
using WcfEntitiesSample.Data;
namespace WcfEntitiesSample.DataAccessTest
{
public class ProductManagerTestData
{
public static void AddTestData(IAdventureWorksDbContext context)
{
context.vProductProductInventories.AddOrUpdate(
new vProductProductInventory { ProductID = 1, Name = "Adjustable Race" }
);
context.vProductProductInventories.AddOrUpdate(
new vProductProductInventory { ProductID = 316, Name = "Blade" }
);
context.SaveChanges();
}
}
}
Next setup your unit testing class:
using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using AdventureWorksEntityFramework.BusinessLogic;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
[TestClass]
public class EffortUnitTests
{
private ITestDatabase _effortDatabaseStrategy;
private IAdventureWorksDbContext _context;
private ProductManager _productRepository;
[TestInitialize]
public void SetupTest()
{
_effortDatabaseStrategy = CreateTestStrategy();
_context = _effortDatabaseStrategy.CreateContext();
ProductManagerTestData.AddTestData(_context);
_productRepository = new ProductManager(_context);
}
protected ITestDatabase CreateTestStrategy()
{
return new EffortDatabaseContext();
}
[TestCleanup]
public void CleanupTest()
{
_effortDatabaseStrategy.Dispose(_context);
_context = null;
}
[TestMethod]
public void GetProducts()
{
Assert.IsTrue(_productRepository.GetProducts().Count > 0);
}
}
}
If you run the unit tests now, they should pass. This completes the basic setup for Effort, so if you only need to work with a simple system and one database, then you are finished. Next, we are going into details about setting up Effort with a complex Entity Framework application that has multiple databases.
Part 2: Using Effort in Complex Apps
Please setup the Northwind database on your same server. You can obtain Northwind from here.
It is important to design applications to be loosely coupled. This allows the developer to easily switch out different layers of the application, such as changing from SQL Server to Oracle for the database layer or changing from having a WPF UI to an ASP.NET MVC front end. This also helps improve maintainability, scalability, modularity and flexibility.
One problem with the way my present application has been built is that it is totally dependent on the Entity Framework. Let us resolve this issue by using interfaces and other n-tier best practices.
Add another ADO.NET Entity Data Model for the Northwind database, and select the Customers table in the Entity Data Model Wizard. Then add a new interface:
using AdventureWorksEntityFramework.DataAccess;
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
namespace AdventureWorksEntityFramework.DataAccess
{
public interface INorthwindDbContext : IDisposable
{
DbContextConfiguration Configuration { get; }
Database Database { get; }
DbSet<Customer> Customers { get; set; }
int SaveChanges();
}
}
Next update the Northwind.Context.cs file:
namespace AdventureWorksEntityFramework.DataAccess
{
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
public partial class NORTHWNDEntities : DbContext, INorthwindDbContext
{
public NORTHWNDEntities()
: base("name=NORTHWNDEntities")
{
}
public NORTHWNDEntities(string nameOrConnectionString)
: base(nameOrConnectionString)
{
this.Configuration.LazyLoadingEnabled = false;
}
public NORTHWNDEntities(DbConnection connection)
: base(connection, true)
{
this.Configuration.LazyLoadingEnabled = false;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public virtual DbSet<Customer> Customers { get; set; }
}
}
Then in your test project, create a ConnectionProviderBase:
using Effort;
using System;
using System.Data.Common;
namespace AdventureWorksEntityFramework.BusinessLogicTest
{
public class ConnectionProviderBase : IDisposable
{
protected DbConnection _connection;
protected void CreateConnection()
{
if (_connection == null)
{
_connection = DbConnectionFactory.CreateTransient();
}
}
public void Dispose()
{
if (_connection != null)
{
_connection.Dispose();
}
}
}
}
Also, create a connection provider for your Northwind database and update the connection provider for your AdventureWorks database:
using System.Data.Common;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
public class EffortAdventureWorksDatabaseContext :
ConnectionProviderBase, ITestAdventureWorksDatabase
{
public IAdventureWorksDbContext CreateContext()
{
base.CreateConnection();
var context = new AdventureWorksEntities(_connection);
return context;
}
public void Dispose(IAdventureWorksDbContext context)
{
if (context != null)
{
context.Dispose();
}
}
}
}
using System.Data.Common;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
public class EffortNorthwindDatabaseContext : ConnectionProviderBase, ITestNorthwindDatabase
{
public INorthwindDbContext CreateContext()
{
base.CreateConnection();
var context = new NORTHWNDEntities(_connection);
return context;
}
public void Dispose(INorthwindDbContext context)
{
if (context != null)
{
context.Dispose();
}
}
}
}
Also, create an interface specifically for testing Northwind and some test data:
using System.Data.Common;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
public class EffortNorthwindDatabaseContext : ConnectionProviderBase, ITestNorthwindDatabase
{
public INorthwindDbContext CreateContext()
{
base.CreateConnection();
var context = new NORTHWNDEntities(_connection);
return context;
}
public void Dispose(INorthwindDbContext context)
{
if (context != null)
{
context.Dispose();
}
}
}
}
using System;
using System.Data.Entity.Migrations;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
public class CustomerManagerTestData
{
public static void AddTestData(INorthwindDbContext context)
{
context.Customers.AddOrUpdate(
new Customer { CustomerID = "1", CompanyName = "Amco" }
);
context.Customers.AddOrUpdate(
new Customer { CustomerID = "2", CompanyName = "Acme" }
);
context.SaveChanges();
}
}
}
using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using AdventureWorksEntityFramework.BusinessLogic;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
[TestClass]
public class EfforNorthwindUnitTests
{
private ITestNorthwindDatabase _effortDatabaseStrategy;
private INorthwindDbContext _context;
private CustomerManager _customerRepository;
[TestInitialize]
public void SetupTest()
{
_effortDatabaseStrategy = CreateTestStrategy();
_context = _effortDatabaseStrategy.CreateContext();
CustomerManagerTestData.AddTestData(_context);
_customerRepository = new CustomerManager(_context);
}
protected ITestNorthwindDatabase CreateTestStrategy()
{
return new EffortNorthwindDatabaseContext();
}
[TestCleanup]
public void CleanupTest()
{
_effortDatabaseStrategy.Dispose(_context);
_context = null;
}
[TestMethod]
public void GetProductsFromEffort()
{
Assert.IsTrue(_customerRepository.GetCustomers().Count > 0);
}
}
}
Also, create this interface for testing with the Northwind database.
using System.Data.Common;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
public class EffortAdventureWorksDatabaseContext : ConnectionProviderBase, ITestAdventureWorksDatabase
{
public IAdventureWorksDbContext CreateContext()
{
base.CreateConnection();
var context = new AdventureWorksEntities(_connection);
return context;
}
public void Dispose(IAdventureWorksDbContext context)
{
if (context != null)
{
context.Dispose();
}
}
}
}
using System.Data.Common;
using AdventureWorksEntityFramework.DataAccess;
namespace WcfEntitiesSample.DataAccessTest
{
public class EffortNorthwindDatabaseContext : ConnectionProviderBase, ITestNorthwindDatabase
{
public INorthwindDbContext CreateContext()
{
base.CreateConnection();
var context = new NORTHWNDEntities(_connection);
return context;
}
public void Dispose(INorthwindDbContext context)
{
if (context != null)
{
context.Dispose();
}
}
}
}
Part 3: Using Repository and Unit of Works Patterns
The above are aimed at creating an abstraction layer between the data access layer and the business logic layer, which helps insulate your application from changes in the data store and also helps with TDD and unit testing.
The next steps are to create a repository for each entity type. This also makes it easy to switch between an in-memory data store collection and production database data store since interfaces are used.
The unit of work class creates a single database context that can be used by all repositories.
Create the following interface in the Data Access Layer:
using System;
using System.Collections.Generic;
namespace AdventureWorksEntityFramework.DataAccess
{
public interface IProductRepository : IDisposable
{
IEnumerable<Product> GetProducts();
Product GetProductByID(int productId);
void InsertProduct(Product product);
void DeleteProduct(int productID);
void UpdateProduct(Product product);
void Save();
}
}
Also, create the following repository in the Data Access Layer:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Entity;
namespace AdventureWorksEntityFramework.DataAccess
{
public class ProductRepository : IProductRepository, IDisposable
{
private AdventureWorksEntities context;
public ProductRepository(AdventureWorksEntities context)
{
this.context = context;
}
public IEnumerable<product> GetProducts()
{
return context.Products.ToList();
}
public Product GetProductByID(int id)
{
return context.Products.Find(id);
}
public void InsertProduct(Product product)
{
context.Products.Add(product);
}
public void DeleteProduct(int productID)
{
Product product = context.Products.Find(productID);
context.Products.Remove(product);
}
public void UpdateProduct(Product product)
{
context.Entry(product).State = EntityState.Modified;
}
public void Save()
{
context.SaveChanges();
}
private bool disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
context.Dispose();
}
}
this.disposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
}
</product>
Notice that the constructor above accepts a database context, which allows all the repositories to use a single context, using a unit of work. Please find details below.
Now you can use the repository instead of the database context wherever needed:
private IProductRepository productRepository;
For example, in the unit tests or in your controller you can use the repository instead of the database context, and in the parameterless constructor just create a new database context object:
private IProductRepository _productRepository;
public ProductManager()
{
this._productRepository = new ProductRepository(new AdventureWorksEntities());
}
public ProductManager(IProductRepository productRepository)
{
this._productRepository = productRepository;
}
public List<product> GetProducts()
{
IEnumerable<product> products = null;
products = (from p in _productRepository.GetProducts()
select new { ProductID = p.ProductID, Name = p.Name }).ToList()
.Select(x => new Product { ProductID = x.ProductID, Name = x.Name });
return products.ToList();
}
</product>
Create a Generic Repository
To remove a lot of duplicate code you can create a generic repository, like:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Entity;
using System.Linq.Expressions;
namespace AdventureWorksEntityFramework.DataAccess
{
public class GenericRepository<tentity> where TEntity : class
{
internal AdventureWorksEntities context;
internal DbSet<tentity> dbSet;
public GenericRepository(AdventureWorksEntities context)
{
this.context = context;
this.dbSet = context.Set<tentity>();
}
public virtual IEnumerable<tentity> Get(
Expression<func<tentity, bool="">> filter = null,
Func<iqueryable<tentity>, IOrderedQueryable<tentity>> orderBy = null,
string includeProperties = "")
{
IQueryable<tentity> query = dbSet;
if (filter != null)
{
query = query.Where(filter);
}
foreach (var includeProperty in includeProperties.Split
(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
query = query.Include(includeProperty);
}
if (orderBy != null)
{
return orderBy(query).ToList();
}
else
{
return query.ToList();
}
}
public virtual TEntity GetByID(object id)
{
return dbSet.Find(id);
}
public virtual void Insert(TEntity entity)
{
dbSet.Add(entity);
}
public virtual void Delete(object id)
{
TEntity entityToDelete = dbSet.Find(id);
Delete(entityToDelete);
}
public virtual void Delete(TEntity entityToDelete)
{
if (context.Entry(entityToDelete).State == EntityState.Detached)
{
dbSet.Attach(entityToDelete);
}
dbSet.Remove(entityToDelete);
}
public virtual void Update(TEntity entityToUpdate)
{
dbSet.Attach(entityToUpdate);
context.Entry(entityToUpdate).State = EntityState.Modified;
}
}
}
</tentity>
Notice above that function allows you to specify values for a where clause filter and an ordering. By passing these values in here, you are ensuring that the filtering work is performed on the database server rather than, say, on a web server if you had them in an ASP.NET MVC controller.
Create a Unit of Work Class
The main purpose for the Unit of Work class is so that if you use multiple repositories, your application only shares a single database context.
Add the following UnitOfWork class to your Data Access Layer:
using System;
namespace AdventureWorksEntityFramework.DataAccess
{
public class UnitOfWork : IDisposable
{
private IAdventureWorksDbContext _context = null;
private GenericRepository<product> _productRepository;
public UnitOfWork()
{
_context = new AdventureWorksEntities();
}
public UnitOfWork(IAdventureWorksDbContext context)
{
_context = context;
}
public GenericRepository<product> ProductRepository
{
get
{
if (this._productRepository == null)
{
this._productRepository = new GenericRepository<product>(_context);
}
return _productRepository;
}
}
public void Save()
{
_context.SaveChanges();
}
private bool disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
_context.Dispose();
}
}
this.disposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
}
</product>
Points of Interest
Spent time reading Tamas Flamich's blog post, but didn't complete the database context factory, which I think would be a worthwhile addition to do when I get the chance.
History
- 2015.10.06 Version 1
- 2015.12.09 Version 2: Added code that demonstrates repositories with unit of work
References
