Unit testing EF Core using in-memory database with an eager-loaded function

Question

I am writing unit tests for my my Web API and cannot get the test to pass except by removing the include (eager-loading from the method). I am using the in-memory database to provide the dbcontext and can't figure out why it is returning no data. Thanks in advance for any help or constructive criticism

This is the method I am trying to test.
Note: it passes the test if I comment out the .include statements.

    public async Task<LibraryAsset> GetAsset(int assetId)
    {
        var asset = await _context.LibraryAssets
            .Include(p => p.Photo)
            .Include(p => p.Category)
            .Include(a => a.AssetType)
            .Include(s => s.Status)
            .Include(s => s.Author)
            .FirstOrDefaultAsync(x => x.Id == assetId);

        return asset;
    }

This is the base DbContext using the inMemory DB:

    public DataContext GetDbContext()
    {
        var builder = new DbContextOptionsBuilder<DataContext>();

        if (useSqlite)
        {
            // Use Sqlite DB.
            builder.UseSqlite("DataSource=:memory:", x => { });
        }
        else
        {
            // Use In-Memory DB.
            builder.UseInMemoryDatabase(Guid.NewGuid().ToString());
        }

        var DataContext = new DataContext(builder.Options);

        if (useSqlite)
        {
            // SQLite needs to open connection to the DB.
            // Not required for in-memory-database and MS SQL.
            DataContext.Database.OpenConnection();
        }

        DataContext.Database.EnsureCreated();

        return DataContext;
    }

This is the test:

    [Fact]
    public async void GetAssetById_ExistingAsset_ReturnAsset()
    {
        using (var context = GetDbContext())
        {
            ILogger<LibraryAssetService> logger = new 
            NullLogger<LibraryAssetService>();

            var service = new LibraryAssetService(context, _logger);

            var asset = new LibraryAsset
            {
                Id = 40,
                NumberOfCopies = 20,
                Title = "",
                Year = 1992,
                Status = new Status { Id = 1 },
                AssetType = new AssetType { Id = 1 },
                Author = new Author { Id = 1 },
                Category = new Category { Id = 2 },
                Photo = new AssetPhoto { Id = 1 }
            };

            context.LibraryAssets.Attach(asset);

            context.Add(asset);
            context.SaveChanges();

            var actual = await service.GetAsset(40);
            Assert.Equal(40, actual.Id);
        }
    }

This is my first time writing unit tests and I am basically learning as I go. Please feel free to point out any other mistakes that you may have noticed as well.

Nikolos

Do you get any error or unexpected result? I made a test with your code, but fail to reproduce your issue. Share us a demo which could reproduce your issue. – Staffan

Answer 1

There are some issues with your code:

1. If your real databse is relational avoid using UseInMemoryDatabase database for testing because it doesn't support relational behaviours.
2. Separate the Arrange contexts from the Act contexts. That means, create a new DataContext for preparing the test, adding test data, and etc, and create another one for SUT (LibraryAssetService in this case). DbContext stores local data (in memory) which may not exist in the database and that could show fake green tests in some scenarios!
3. You don't need Attach when you're adding the assets. That could create Foreign key constraint error with sqlite.

I removed some of your navigations and parameters for the sake of simplicity. So lets suppose the LibraryAssetService is something like this:

public class LibraryAssetService
{
  public LibraryAssetService(DataContext context)
  {
     _context = context;
  }

  private readonly DataContext _context;

  public async Task<LibraryAsset> GetAsset(int assetId)
  {
     var asset = await _context.LibraryAssets
        .Include(p => p.Photo)
        .Include(s => s.Author)
        .FirstOrDefaultAsync(x => x.Id == assetId);

     return asset;
  }
}

The test class:

public class LibraryAssetServiceTests
{
  public LibraryAssetServiceTests()
  {
     _factory = new TestDataContextFactory();
  }

  private TestDataContextFactory _factory;

  [Fact]
  public async void GetAssetById_ExistingAsset_ReturnAsset()
  {
     // Arrange
     using (var context = _factory.Create())
     {
        var asset = new LibraryAsset
        {
           Id = 40,
           Author = new Author { Id = 1 },
           Photo = new Photo { Id = 1 }
        };

        context.Add(asset);
        context.SaveChanges();
     }

     // Act
     using (var context = _factory.Create())
     {
        var service = new LibraryAssetService(context);
        var actual = await service.GetAsset(40);

        // Assert
        Assert.Equal(40, actual.Id);
        Assert.Equal(1, actual.Author.Id);
        Assert.Equal(1, actual.Photo.Id);
     }

  }
}

And finally, a little helper class to prepare the DataContext for your tests. It's good practice to extract these kind of things outside your test classes. The important thing to remember when testing with sqlite memory databases is that you should keep the connection open during the test. No matter how many DbContext instances you create. The xUnit create an instance of the test class for each test method. So an instance of TestDataContextFactory will be created for each test, and you are good to go.

public class TestDataContextFactory
{
  public TestDataContextFactory()
  {
     var builder = new DbContextOptionsBuilder<DataContext>();
     var connection = new SqliteConnection("DataSource=:memory:");
     connection.Open();
     builder.UseSqlite(connection);

     using (var ctx = new DataContext(builder.Options))
     {
        ctx.Database.EnsureCreated();
     }

     _options = builder.Options;
  }

  private readonly DbContextOptions _options;

  public DataContext Create() => new DataContext(_options);
}

Obie

I am getting a foreign key restriction error after using your code. I tried adding the suppressForeignkeyenforcement but that still did not help – Nikolos

create a github repo, I will check it. The code worked ok on my machine. Make sure you are not using the Attach() method. – Obie

This is the link to the github repo link – Nikolos

@lawrencefejokwu make breakpoint at line 34 of LibraryAssetService and watch your asset object. As you see there are lots of 0 values for foreign key values like StatusId, AssetTypeId, or AuthorId. That's why the foreign key restriction is happening. There is no Author in the database with ID of 0! I commented out all the foreign keys in LibraryAsset and made the test to run with Sqlite, and it was green! Make sure you initialize your asset object with whatever it needs and you are good to go. – Obie

@lawrencefejokwu and one more thing: I checked the DataContext class, you didn't configure the asset entity. Define the entity relations for EF, that could make your entities much smaller and clearer. And be aware of the seeding in OnModelCreating method. It can cost you performance issues. – Obie

you were right, everything is working as expected. i will look into refactoring the DataContext eventually, although this is really just a side project that i am using to learn .net core and not necessarily going into PROD. however i do want to make sure i am learning the best practices. Thank you so much for all your help. – Nikolos

Your welcome @lawrencefejokwu! .NET Core is fun to learn. Have fun! – Obie