Unit test Data Access Layer (Entity Framework) with in-memory database

By | March 28, 2013

Article_EntityFrameworkUnit testing code that uses data from database is a difficult task. If you use Entity Framework you can stub your data with Effort – in-memory Entity Framework data provider. David Bywaters describes how to use Effort if your create you context based in DbContext class. But if you use model first approach (.edmx already exists) you can follow this guide:

First of all install Effort from NuGet:

Install-Package Effort -Pre

Then add add App.Config file to your unit test project. App.Config file should contain connection string:

<?xml version="1.0" encoding="utf-8" ?>
    <add name="YourEntities" connectionString="metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\SqlExpress;initial catalog=YourDatabase;integrated security=True;pooling=False;multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Replace DataModel with name of your .edmx file.

After that you can create connection to your in-memory DB:

EntityConnection connection = EntityConnectionFactory.CreateTransient("name=YourEntities");

You should use this connection to fill in-memory DB:

using (var context = new YourEntities(connection))
                    new User
                            Id = Guid.NewGuid(),
                            Name = "test",

And now you can use this connection for test your DAL classes. For example you have manager that retrieves data from DB and convert this data to your business object:

public class UserManager
        private readonly EntityConnection connection;

        public UserManager()

        public UserManager(EntityConnection connection)
            this.connection = connection;

        public virtual UserBO GetUser(Guid userId)
            using (var dbContext = connection == null ? new YourEntities() : new YourEntities(connection))
                var user = dbContext.Users.FirstOrDefault(item => item.Id == userId);

                if (user == null)
                    throw new DataException(string.Format("User with id {0} not found", userId));

                UserBO result = new UserBO();
                result.Name = user.Name;
                result.NameUpper = user.Name.ToUpper();
                return result;

So now you can test your DAL (manager class) with in-memory DB.


2 thoughts on “Unit test Data Access Layer (Entity Framework) with in-memory database

  1. CSLewis


    Our application was developed with Entity framework’s Model First approach. However, it seems that Effort Testing Tool needs to be see the actual SQL Server Database that the application uses in order to mock our Entity Framework’s DbContext which seems to going against proper Unit Testing principles.
    Could you please read the stackoverflow posting below that I posted, and make some suggestions?


    Thanks in advance.


Leave a Reply

Your email address will not be published. Required fields are marked *