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();
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,
};
}
}
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,
};
}
}
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>();
}
IAuthors.cs
using BookshelfSample.Models;
namespace BookshelfSample.Books;
public interface IAuthors
{
Task<Author> GetOrCreateAsync(string name);
Task<List<Author>> GetByNameAsync(string name);
}
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();
}
}
SearchBookCriteria.cs
namespace BookshelfSample.Books;
public record struct SearchBookCriteria(string? Name, string? AuthorName);
IBooks.cs
using BookshelfSample.Models;
namespace BookshelfSample.Books;
public interface IBooks
{
Task CreateAsync(Author author, Book newItem);
Task<List<Book>> GetAsync(SearchBookCriteria criteria);
}
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();
}
}
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;
}
}
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);
}
}
- EF Core testing sample - Microsoft Docs
- Using SQLite to test an EF Core application - Microsoft Docs
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(),
};
}
}
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);
}
}
Exception
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(),
};
}
}
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);
...
Exception
Top comments (1)
Great article on testing Entity Framework Core applications with in-memory SQLite and xUnit! For developers working on macOS who need to run .NET Framework 4 applications without setting up a virtual machine, ServBay offers a seamless solution. It's been a game-changer for my development workflow.