Writing unit-test using EF6 (Entity Framework 6)

Question

I have an ASP.NET Core project using .NET Framework 4.6.1 and EF6. Now I want to write some unit tests and already spent hours to configure an in-memory SQLite database for using EF6. But it doesn't work.

So, the question is how can I test my project using EF6 without any mocks (rather an in-memory database)?

My current code:

public class DataAccessLayer : DbContext
{
  public DataAccessLayer(string connectionString)
     : base(connectionString) {
  }

  public DataAccessLayer(DbConnection connection)
     : base(connection, true) {
  }

  public DbSet<User> Users { get; set; }

  public DbSet<Setting> Settings { get; set; }

  public DbSet<UserRole> UserRoles { get; set; }

  public DbSet<MainKey> MainKeys { get; set; }
}

[Table("Users")]
public class User
{
  [Key]
  [Required]
  public int UserID { get; set; }

  [Required][StringLength(50)]
  public string UserName { get; set; }

  ...
}

public class Testbase
{
  protected DataAccessLayer Context { get; private set; }

  [TestInitialize]
  public virtual void SetUp()
  {
     var connection = this.CreateConnection();
     connection.Open();
     this.Context = new DataAccessLayer(connection);
     this.Context.Database.CreateIfNotExists();
  }

  private SQLiteConnection CreateConnection() {
     var connectionStringBuilder = new SQLiteConnectionStringBuilder { DataSource = ":memory:" };
     return new SQLiteConnection(connectionStringBuilder.ToString());
  }
}

If I try to add a user, I get following error:

System.Data.SQLite.SQLiteException: SQL logic error or missing database no such table: Users.

I assume that my tables are generated by calling this.Context.Database.CreateIfNotExists();, or am I mistaken about that?

Answer 1

Consider using the Nuget package Effort

It is a simple and fast in-memory database used for unit testing.

You can start it with an empty database and fill it yourself using a database seeder, or you can fill it with values from a test CSV file.

See Tutorials Effort - Entity Framework Unit Testing Tool

Simple example with a database with Blogs and Posts. A one-to-many relationship between Blogs and Posts

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Post> Posts { get; set; } 
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; } 
}

public class BloggingContext : DbContext
{
    public BloggingContext() : base() { } // constructor using config file

    public BloggingContext(string nameOrConnectionString) : base(nameOrConnectionString) { }
    public BloggingContext(DbConnection connection) : base(connection, true) { }
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

You don't get a connection string to a database, instead you get a DbConnection to it. Hence the third constructor for the BloggingContext.

The Boolean passed to the super class in this constructor is to tell the DbContext that it owns the connection: the DbContext should close and Dispose the connection when the it is Disposed.

This is the only difference with your normal DbContext usage. All other calls to DbContext and DbSets are normal.

Example

static void Main(string[] args)
{
    var connection = Effort.DbConnectionFactory.CreateTransient();

    using (var dbContext = new BloggingContext(connection))
    {
        var addedBlog = dbContext.Blogs.Add(new Blog[]
        {
            Name = "1",
            Posts = new Post[]
            { 
                new Post() {Title = "1st", Content = "a"},
                new Post() {Title = "2nd", Content = "b"},
                new Post() {Title = "3rd", Content = "c"},
            },
        });
        dbContext.SaveChanges();
    }

    using (var dbContext = new BloggingContext(connection))
    {
        var allPosts = dbContext.Posts.ToList();
        foreach (var post in allPosts)
        {
            Console.WriteLine($"{post.Id}: {post.Title}");
        }
    }

One tip: while developing it is sometimes difficult to see whether the test fails because of incorrect test (data) or because of incorrect code being tested. It is fairly difficult to check during debugging what is in the database during the test. Hence I tend to develop the tests with a real database filled with test values, and once debugging of the tests is seldom needed, switch to the in-memory database. In fact, for me this is a switch between the second or the third DbContext constructor