How to unit test repositories using Entity Framework

Question

I am building a system using Entity Framework as as my repository, and while i agree that i should not be testing Entity Framework i would like to be able to verify my logic around Entity Framework in my repositories. However i have not been able to find a real solution to this either here on Stack Overflow or somewhere else. Most questions just ends up in people saying don't test the framework.

In my example here i have A repository that take an BillEntity type and stores it as BillDal, now the storage plan for my bills are different from my entities. Example A Bill entity has Line Items that in turn has Allocations, also they do not have Ids'. However my storage objects stores the line items and allocations as flat objects, on the bill. This is all repository logic, and the mapping and calculations of this should be tested. Later on i wanted to replace my dum, clear and and readd logic with more complex mapping logic that also needs to be tested.

I have tried with using a SQLLite in memory database, but it can't be created as i have 'Collation' on my model defined such as modelBuilder.UseCollation("utf8mb4_unicode_520_ci") my database is MariaDb. Dose a Testing Framework exists that can drop in and mock EntityFramework, without me having to mock everything myself? I know i could just test my helper methods, but i would also like to be able to write functional tests for my Repository.

Here is my Repository code that i would like to unit test:

public class BillsMySqlRepository : BaseMySQLRepository, IBillsRepository
{
    public async Task<BillEntity> GetById(long id)
    {
        await using var context = await this.contextFactory.CreateDbContextAsync();
        var dal = await context.Bills
            .Include(x => x.LineItems)
            .Include(x => x.Allocations)
            .FirstOrDefaultAsync(x => x.Id == id);
        this.ThrowNotFoundIfNull(dal, "Bill");
        var entity = this.mapper.Map<BillEntity>(dal);
        entity.LineItems = this.CreateLineItemEntities(dal);
        return entity;
    }

    private List<BillLineItemEntity> CreateLineItemEntities(BillDal dal)
    {
        var lineItemEntities = new List<BillLineItemEntity>();
        var allocationMap = this.CreateAllocationMap(dal.Allocations);
        foreach (var lineItemDal in dal.LineItems)
        {
            var lineItemEntity = this.mapper.Map<BillLineItemEntity>(lineItemDal);
            lineItemEntity.Allocations = allocationMap.TryGetValue(lineItemDal.Id, out var value)
                ? value.Select(x => this.mapper.Map<BillAllocationEntity>(x)).ToList()
                : new List<BillAllocationEntity>();
            lineItemEntities.Add(lineItemEntity);
        }

        return lineItemEntities;
    }

    private Dictionary<long, List<BillAllocationDal>> CreateAllocationMap(List<BillAllocationDal> allocationDals)
    {
        var allocationMap = new Dictionary<long, List<BillAllocationDal>>();
        foreach (var allocation in allocationDals)
        {
            if (!allocationMap.ContainsKey(allocation.LineItemId))
            {
                allocationMap.Add(allocation.LineItemId, new List<BillAllocationDal>());
            }

            allocationMap[allocation.LineItemId].Add(allocation);
        }

        return allocationMap;
    }

    public async Task<long> Create(CreateOrUpdateBillEntity entity)
    {
        Guard.Against.InvalidCreateOrUpdateBillEntity(entity, nameof(entity));
        await using var context = await this.contextFactory.CreateDbContextAsync();
        var dal = this.mapper.Map<BillDal>(entity);
        this.AddId(dal);
        dal.CreatedMilliseconds = DateTimeOffset.Now.ToUnixTimeMilliseconds();
        dal.UpdatedMilliseconds = DateTimeOffset.Now.ToUnixTimeMilliseconds();
        this.AddLineItemAndAllocations(dal, entity);
        this.UpdateMetadataFields(dal, entity);
        context.Bills.Add(dal);
        await context.SaveChangesAsync();
        return dal.Id;
    }

    public async Task Update(long id, CreateOrUpdateBillEntity entity)
    {
        Guard.Against.NegativeOrZero(id, nameof(id));
        Guard.Against.InvalidCreateOrUpdateBillEntity(entity, nameof(entity));
        await using var context = await this.contextFactory.CreateDbContextAsync();
        var dal = await context.Bills
            .Include(x => x.LineItems)
            .Include(x => x.Allocations)
            .FirstOrDefaultAsync(x => x.Id == id);
        this.ThrowNotFoundIfNull(dal, "Bill");

        // TODO: Later we need to make this smarter that matches and updates instead of just clearing and re-adding
        context.BillLineItems.RemoveRange(dal.LineItems);
        context.BillAllocations.RemoveRange(dal.Allocations);
        this.AddLineItemAndAllocations(dal, entity);
        // TODO DONE

        this.UpdateMetadataFields(dal, entity);
        await context.SaveChangesAsync();
    }
}

Dals:

[Table("bills")]
[Index(nameof(CompanyId), Name = "index_bills_company_id")]
public class BillDal : IIdDal, ICompanyIdDal
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("id")]
    public long Id { get; set; }

    [Required]
    [Column("company_id")]
    public long CompanyId { get; set; }

    [StringLength(150)]
    [Column("reference")]
    public string? Reference { get; set; }

    [Column("memo")]
    public string? Memo { get; set; }

    [Column("source")]
    public long? Source { get; set; }

    [Column("source_type")]
    public BillSource? SourceType { get; set; }

    [Column("destination")]
    public long? Destination { get; set; }

    [Column("destination_type")]
    public BillSource? DestinationType { get; set; }

    [Column("property_id")]
    public long PropertyId { get; set; }

    [Column("bill_date_ms")]
    public long BillDateMilliseconds { get; set; }

    [Column("due_date_ms")]
    public long? DueDateMilliseconds { get; set; }

    [Required]
    [Column("create_ms")]
    public long CreatedMilliseconds { get; set; }

    [Required]
    [Column("updated_ms")]
    public long UpdatedMilliseconds { get; set; }

    // The following field are use as metadata to be able to fetch the bills quicker
    [Required]
    [Column("amount")]
    public decimal Amount { get; set; }

    [Required]
    [Column("amount")]
    public decimal Due { get; set; }


    [ForeignKey(nameof(CompanyId))]
    public virtual CompanyDal Company { get; set; }

    public virtual List<BillAllocationDal> Allocations { get; set; }
    public virtual List<BillLineItemDal> LineItems { get; set; }
}

[Table("bills_allocations")]
public class BillAllocationDal : IIdDal, ICompanyIdDal
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("id")]
    public long Id { get; set; }

    [Required]
    [Column("company_id")]
    public long CompanyId { get; set; }

    [Required]
    [Column("bill_id")]
    public long BillId { get; set; }

    [Required]
    [Column("bill_line_item_id")]
    public long LineItemId { get; set; }

    [Required]
    [Column("transaction_id")]
    public long TransactionId { get; set; }

    [Required]
    [Column("amount")]
    public decimal Amount { get; set; }

    [ForeignKey(nameof(BillId))]
    public virtual BillDal Bill { get; set; }

    [ForeignKey(nameof(CompanyId))]
    public virtual CompanyDal Company { get; set; }

    [ForeignKey(nameof(TransactionId))]
    public virtual TransactionDal Transaction { get; set; }

    [ForeignKey(nameof(LineItemId))]
    public virtual BillLineItemDal LineItem { get; set; }
}
[Table("bills_line_items")]
public class BillLineItemDal : IIdDal, ICompanyIdDal
{
    [Required]
    [Column("bill_id")]
    public long BillId { get; set; }

    [Required]
    [Column("chart_of_account_id")]
    public long ChartOfAccountId { get; set; }

    [StringLength(1024)]
    [Column("description")]
    public string Description { get; set; }

    [Required]
    [Column("amount")]
    public decimal Amount { get; set; }

    [ForeignKey(nameof(BillId))]
    public virtual BillDal Bill { get; set; }

    [ForeignKey(nameof(CompanyId))]
    public virtual CompanyDal Company { get; set; }

    [Required]
    [Column("company_id")]
    public long CompanyId { get; set; }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [Column("id")]
    public long Id { get; set; }
}

My Entities:

public class BillEntity
{
    public long Id { get; set; }
    public long CompanyId { get; set; }
    public string? Reference { get; set; }
    public string? Memo { get; set; }
    public long VendorId { get; set; }
    public long PropertyId { get; set; }
    public DateTimeOffset BillDate { get; set; }
    public DateTimeOffset? DueDate { get; set; }

    public List<BillLineItemEntity> LineItems { get; set; }
}

public class BillLineItemEntity
{
    public long ChartOfAccountId { get; set; }
    public string? Description { get; set; }
    public decimal Amount { get; set; }
    public List<BillAllocationEntity> Allocations { get; set; }
}
public class BillAllocationEntity
{
    public long TransactionId { get; set; }
    public decimal Amount { get; set; }
}

Answer 1

After some research I found the best option to use the SQLite in-memory database to be able to test my repositories. However this left the problem of SQLite not supporting the "utf8mb4_unicode_520_ci" collation. To get around this I update my OnModelCreating, not to use this when the Provider is SQLite, this means that migrations files will still be generated with the correct Collation and CharSet.

if (Database.ProviderName != "Microsoft.EntityFrameworkCore.Sqlite") {
    modelBuilder.HasCharSet("utf8mb4");
    modelBuilder.UseCollation("utf8mb4_unicode_520_ci");
}

I then create a TestContextFactory that uses the EnsureCreated(), as this will not use the migration files generated that has the collation. But will instead generated the database and tables from the Context and Dal directly.

public class MagicContextTestFactory : IDbContextFactory<MagicContext>
{
    private readonly SqliteConnection _connection;

    static MagicContextTestFactory()
    {
        // Initialize the SQLite provider
        SQLitePCL.Batteries.Init();
    }

    public MagicContextTestFactory()
    {
        _connection = new SqliteConnection("Filename=:memory:");
        _connection.Open();
    }

    public MagicContext CreateDbContext()
    {
        var optionsBuilder = new DbContextOptionsBuilder<MagicContext>();
        optionsBuilder.UseSqlite(_connection);

        var context = new MagicContext(optionsBuilder.Options);
        context.Database.EnsureCreated();

        return context;
    }
}

Answer 2

One justification of a Repository pattern overtop the EF DbContext/DbSets is to facilitate a boundary for testing, meaning the Repository is what you Mock, not what you test. You don't need to write tests to confirm EF does what it is supposed to, or that Automapper does what it is supposed to. Those products are already tested and should be trusted. The repository should not contain any business logic that would need testing. Worst case if there is some boundary logic such as handling add/remove scenarios rather than dumping reference lists and re-added references, that logic can be separated into a test-able class. I.e. a metho that is provided the two list of IDs to compare to find what IDs need to be added and what need to be removed.

With Automapper consider using the ProjectTo method instead of loading entities with eager-loaded associations, calling Map and then itterating over relations. This can produce far more efficient queries, reducing the size of Cartesian Products that JOINs result in.