DEV Community 👩‍💻👨‍💻

Masui Masanori
Masui Masanori

Posted on

【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

Top comments (0)

DEV

Thank you.

 
Thanks for visiting DEV, we’ve worked really hard to cultivate this great community and would love to have you join us. If you’d like to create an account, you can sign up here.