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

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(); 

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, }; } } 

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, }; } } 

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>(); } 

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

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(); } } 

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

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

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(); } } 

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; } } 

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); } } 

... 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(), }; } } 

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); } } 

... 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(), }; } } 

... 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); ...