TheLazy.Dev
Portfolio GitHub

Blog

csharp |xunit |entityframeworkcore

【ASP.NET Core】【xUnit】Testing Entity Framework Core applications with in-memory SQLite

masanori_msl

Masui Masanori

Posted on January 9, 2022

【ASP.NET Core】【xUnit】Testing Entity Framework Core applications with in-memory SQLite

Intro

In this time, I will try testing Entity Framework Core applications.
As same as I tried testing ASP.NET Core applications last time, I will use xUnit and Moq.

I will use in-memory SQLite in this time.

Environments

  • .NET ver.6.0.101
  • Microsoft.NET.Test.Sdk ver.16.11.0
  • xUnit ver.2.4.1
  • xunit.runner.visualstudio ver.2.4.3
  • coverlet.collector ver.3.1.0
  • Microsoft.EntityFrameworkCore.Sqlite ver.6.0.1
  • Moq ver.4.16.1

Sample project

Program.cs 

using BookshelfSample.Books;
using BookshelfSample.Models;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddDbContext<BookshelfContext>(options =>
                options.UseNpgsql(builder.Configuration["DbConnection"]));
builder.Services.AddScoped<IAuthors, Authors>();
builder.Services.AddScoped<IBooks, Books>();
builder.Services.AddScoped<IBookService, BookService>();
var app = builder.Build();
app.UseStaticFiles();
app.UseRouting();
app.UseEndpoints(endpoints =>
{
    endpoints.MapControllers();
});
app.Run();
Enter fullscreen mode Exit fullscreen mode

Author.cs 

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookshelfSample.Models;

[Table("author")]
public record class Author
{
    [Key]
    [Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; init; }
    [Required]
    [Column("name")]
    public string Name { get; init; } = "";
    public List<Book> Books { get; init; } = new List<Book>();
    public static Author Create(string name)
    {
        return new Author
        {
            Name = name,
        };
    }
}
Enter fullscreen mode Exit fullscreen mode

Book.cs 

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace BookshelfSample.Models;

[Table("book")]
public record class Book
{
    [Key]
    [Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; init; }
    [Required]
    [Column("name")]
    public string Name { get; init; } = "";
    [Required]
    [Column("author_id")]
    public int AuthorId { get; init; }
    public Author Author { get; init; } = new Author();
    public static Book Create(Author author, Book value)
    {
        return new Book
        {
            Name = value.Name,
            AuthorId = author.Id,
        };
    }
}
Enter fullscreen mode Exit fullscreen mode

BookshelfContext.cs 

using BookshelfSample.Models.SeedData;
using Microsoft.EntityFrameworkCore;
namespace BookshelfSample.Models;

public class BookshelfContext: DbContext
{
    public BookshelfContext(DbContextOptions<BookshelfContext> options)
            : base(options)
    {
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .HasOne(b => b.Author)
            .WithMany(a => a.Books)
            .HasForeignKey(b => b.AuthorId);
    }
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();
}
Enter fullscreen mode Exit fullscreen mode

IAuthors.cs 

using BookshelfSample.Models;
namespace BookshelfSample.Books;
public interface IAuthors
{
    Task<Author> GetOrCreateAsync(string name);
    Task<List<Author>> GetByNameAsync(string name);
}
Enter fullscreen mode Exit fullscreen mode

Authors.cs 

using BookshelfSample.Models;
using Microsoft.EntityFrameworkCore;

namespace BookshelfSample.Books;
public class Authors: IAuthors
{
    private readonly BookshelfContext context;
    private readonly ILogger<Authors> logger;
    public Authors(BookshelfContext context,
        ILogger<Authors> logger)
    {
        this.context = context;
        this.logger = logger;
    }
    public async Task<Author> GetOrCreateAsync(string name)
    {
        var exited = await this.context.Authors
            .FirstOrDefaultAsync(a => a.Name == name);
        if(exited != null)
        {
            return exited;
        }
        var newAuthor = Author.Create(name);
        await this.context.Authors.AddAsync(newAuthor);
        await this.context.SaveChangesAsync();
        return newAuthor;
    }
    public async Task<List<Author>> GetByNameAsync(string name)
    {
        return await this.context.Authors
            .Where(a => a.Name.Contains(name))
            .ToListAsync();
    }
}
Enter fullscreen mode Exit fullscreen mode

SearchBookCriteria.cs 

namespace BookshelfSample.Books;
public record struct SearchBookCriteria(string? Name, string? AuthorName);
Enter fullscreen mode Exit fullscreen mode

IBooks.cs 

using BookshelfSample.Models;
namespace BookshelfSample.Books;
public interface IBooks
{
    Task CreateAsync(Author author, Book newItem);
    Task<List<Book>> GetAsync(SearchBookCriteria criteria);
}
Enter fullscreen mode Exit fullscreen mode

Books.cs 

using Microsoft.EntityFrameworkCore;
using BookshelfSample.Models;

namespace BookshelfSample.Books;
public class Books: IBooks
{
    private readonly BookshelfContext context;
    private readonly ILogger<Books> logger;
    public Books(BookshelfContext context,
        ILogger<Books> logger)
    {
        this.context = context;
        this.logger = logger;
    }
    public async Task CreateAsync(Author author, Book newItem)
    {
        await this.context.Books.AddAsync(Book.Create(author, newItem));
        await this.context.SaveChangesAsync();
    }
    public async Task<List<Book>> GetAsync(SearchBookCriteria criteria)
    {
        var whereClause = "";
        if(string.IsNullOrEmpty(criteria.Name) == false)
        {
            whereClause = string.Format(" WHERE b.name='{0}'", criteria.Name);
        }
        if(string.IsNullOrEmpty(criteria.AuthorName) == false)
        {
            if(string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE ";
            }
            else
            {
                whereClause += " AND ";
            }
            whereClause = string.Format(" INNER JOIN author a ON b.author_id = a.id{0}a.name LIKE '%{1}%'", whereClause, criteria.AuthorName);
        }
        return await this.context.Books.FromSqlRaw(string.Format("SELECT b.id, b.name, b.author_id FROM book b", whereClause))
            .ToListAsync();
    }
}
Enter fullscreen mode Exit fullscreen mode

Adding tests

Creating Connection and DbContext

To test accessing database classes, I have to connect testing database server and create DbContext.
As I said above, I use in-memory SQLite in this time.

using BookshelfSample.Models;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

namespace BookshelfSampleTest.Models;
public class SharedDatabaseFixture: IDisposable
{
    private readonly SqliteConnection connection;
    public SharedDatabaseFixture()
    {
        this.connection = new SqliteConnection("DataSource=:memory:");
        this.connection.Open();
    }
    public void Dispose() => this.connection.Dispose();
    public BookshelfContext CreateContext()
    {
        var result = new BookshelfContext(new DbContextOptionsBuilder<BookshelfContext>()
            .UseSqlite(this.connection)
            .Options);
        result.Database.EnsureCreated();
        return result;
    }
}
Enter fullscreen mode Exit fullscreen mode

AuthorsTest.cs 

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Moq;
using Xunit;
using BookshelfSample.Books;
using BookshelfSampleTest.Models;

namespace BookshelfSampleTest.Books;
public class AuthorsTest: IDisposable
{
    private readonly SharedDatabaseFixture databaseFixture;
    private readonly Mock<ILogger<Authors>> loggerMock;
    public AuthorsTest()
    {
        this.databaseFixture = new SharedDatabaseFixture();
        this.loggerMock = new Mock<ILogger<Authors>>();
    }
    public void Dispose()
    {
        this.databaseFixture.Dispose();
    }
    [Fact]
    public async Task CreateIfTheNameIsNotExisted()
    {
        using var context = this.databaseFixture.CreateContext();
        var authors = new Authors(context, this.loggerMock.Object);
        // all tables are empty by default
        Assert.Equal(await context.Authors.CountAsync(), 0);

        // add a new item
        var result = await authors.GetOrCreateAsync("Sample");
        Assert.True(await context.Authors.AnyAsync());

        var firstItem = await context.Authors.FirstOrDefaultAsync(a => a.Name == result.Name);
        Assert.Equal(result.Name, firstItem?.Name);   
    }   
}
Enter fullscreen mode Exit fullscreen mode

Data types

Because SQLite can't use some data types, I sometimes get exceptions.

For example, I can't set data type like "timestamp(6) without time zone".

Timestamp

Book.cs 

...
public record class Book
{
...
    [Column("last_update_date", TypeName = "timestamp(6) without time zone")]
    public DateTime LastUpdateDate { get; init; }

    public Author Author { get; init; } = new Author();

    public static Book Create(Author author, Book value)
    {
        return new Book
        {
            Name = value.Name,
            AuthorId = author.Id,
            LastUpdateDate = DateTime.Now.ToUniversalTime(),
        };
    }
}
Enter fullscreen mode Exit fullscreen mode

BooksTest.cs 

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Moq;
using Xunit;
using BookshelfSample.Books;
using BookshelfSampleTest.Models;
using BookshelfSample.Models;
using BookshelfSample.Models.SeedData;

namespace BookshelfSampleTest.Books;
public class BooksTest: IDisposable
{
    private readonly SharedDatabaseFixture databaseFixture;
    private readonly Mock<ILogger<BookshelfSample.Books.Books>> loggerMock;
    private readonly Mock<ILogger<Authors>> authorLoggerMock;
    public BooksTest()
    {
        this.databaseFixture = new SharedDatabaseFixture();
        this.loggerMock = new Mock<ILogger<BookshelfSample.Books.Books>>();
        this.authorLoggerMock = new Mock<ILogger<Authors>>(); 
    }
    public void Dispose()
    {
        this.databaseFixture.Dispose();
    }
    [Fact]
    public async Task AddOne()
    {
        using var context = this.databaseFixture.CreateContext();
        var authors = new Authors(context, this.authorLoggerMock.Object);
        var books = new BookshelfSample.Books.Books(context, this.loggerMock.Object);
        var newAuthor = await authors.GetOrCreateAsync("Sample");
        var newItem = new Book
        {
            Name = "SampleBook",
            AuthorId = newAuthor.Id,
        };
        await books.CreateAsync(newAuthor, newItem);
        Assert.Equal(await context.Books.CountAsync(), 1);
    }
}
Enter fullscreen mode Exit fullscreen mode

Exception

Image description
I can use "timestamp without time zone", "timestamp with time zone", and so on.

decimal

I can't sort by decimal properties.

Book.cs 

...
public record class Book
{
...
    [Column("purchase_date", TypeName = "date")]
    public DateOnly? PurchaseDate { get; init; }
    [Column("price", TypeName = "money")]
    public decimal? Price { get; init; }

    public Author Author { get; init; } = new Author();

    public static Book Create(Author author, Book value)
    {
        return new Book
        {
            Name = value.Name,
            AuthorId = author.Id,
            PurchaseDate = value.PurchaseDate,
            Price = value.Price,
            LastUpdateDate = DateTime.Now.ToUniversalTime(),
        };
    }
}
Enter fullscreen mode Exit fullscreen mode

BooksTest.cs 

...
        using var context = this.databaseFixture.CreateContext();
        var authors = new Authors(context, this.authorLoggerMock.Object);
        var books = new BookshelfSample.Books.Books(context, this.loggerMock.Object);
        var newAuthor = await authors.GetOrCreateAsync("Sample");
        var newItem = new Book
        {
            Name = "SampleBook",
            AuthorId = newAuthor.Id,
            LanguageId = LanguageData.GetEnglish().Id,
            PurchaseDate = DateOnly.FromDateTime(DateTime.Now),
            Price = 3000
        };
        await books.CreateAsync(newAuthor, newItem);
        Assert.Equal(await context.Books.CountAsync(), 1);

        var newItem2 = new Book
        {
            Name = "SampleBook2",
            AuthorId = newAuthor.Id,
            LanguageId = LanguageData.GetEnglish().Id,
            PurchaseDate = DateOnly.FromDateTime(DateTime.Now),
            Price = 3030
        };
        await books.CreateAsync(newAuthor, newItem);
        var b = await context.Books.OrderBy(b => b.Price).ToListAsync();

        Assert.Equal(b.Count, 2);
...
Enter fullscreen mode Exit fullscreen mode

Exception

Image description

💖 💪 🙅 🚩
masanori_msl
Masui Masanori

Posted on January 9, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

[ASP.NET Core][Entity Framework Core] Logging for xUnit projects
aspnetcore [ASP.NET Core][Entity Framework Core] Logging for xUnit projects

January 16, 2022

【ASP.NET Core】【xUnit】Testing Entity Framework Core applications with in-memory SQLite
csharp 【ASP.NET Core】【xUnit】Testing Entity Framework Core applications with in-memory SQLite

January 9, 2022