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

Unit 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" ?>
<configuration>
  <connectionStrings>
    <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" />
  </connectionStrings>
</configuration>

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))
            {
                context.Users.AddObject(
                    new User
                        {
                            Id = Guid.NewGuid(),
                            Name = "test",
                        });
                context.SaveChanges();
            }

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.

Links:

• Effort tutorial