DEV Community

Masui Masanori
Masui Masanori

Posted on

4 1

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

#csharp #xunit #entityframeworkcore

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

profile
Neon
 Promoted

Neon image

Build better on Postgres with AI-Assisted Development Practices

Compare top AI coding tools like Cursor and Windsurf with Neon's database integration. Generate synthetic data and manage databases with natural language.

Read more →

Top comments (1)

Collapse
 
sunshine222025 profile image
SUNSHINE222025 •

​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.​

profile
Stellar Development Foundation
 Promoted

Image of Stellar post

Check out Episode 1: How a Hackathon Project Became a Web3 Startup 🚀

Ever wondered what it takes to build a web3 startup from scratch? In the Stellar Dev Diaries series, we follow the journey of a team of developers building on the Stellar Network as they go from hackathon win to getting funded and launching on mainnet.

Read more

👋 Kindness is contagious

Engage with a wealth of insights in this thoughtful article, valued within the supportive DEV Community. Coders of every background are welcome to join in and add to our collective wisdom.

A sincere "thank you" often brightens someone’s day. Share your gratitude in the comments below!

On DEV, the act of sharing knowledge eases our journey and fortifies our community ties. Found value in this? A quick thank you to the author can make a significant impact.

Okay