【ASP.NET Core】【xUnit】Testing Entity Framework Core applications with in-memory SQLite
Tags: csharp, xunit, entityframeworkcore
Published: 2022-01-09 18:53:15

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

* [【ASP.NET Core】【xUnit】【Moq】Add unit tests 1](https://dev.to/masanori_msl/asp-net-core-xunit-moq-add-unit-tests-1-26c8)

I will use in-memory SQLite in this time.

* [Testing code that uses EF Core - EF Core|Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/testing/)

### 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
```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
```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
```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
```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
```cs
using BookshelfSample.Models;
namespace BookshelfSample.Books;
public interface IAuthors
{
    Task<Author> GetOrCreateAsync(string name);
    Task<List<Author>> GetByNameAsync(string name);
}
```
#### Authors.cs
```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
```cs
namespace BookshelfSample.Books;
public record struct SearchBookCriteria(string? Name, string? AuthorName);
```
#### IBooks.cs
```cs
using BookshelfSample.Models;
namespace BookshelfSample.Books;
public interface IBooks
{
    Task CreateAsync(Author author, Book newItem);
    Task<List<Book>> GetAsync(SearchBookCriteria criteria);
}
```
#### Books.cs
```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.

```cs
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
```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](https://docs.microsoft.com/en-us/ef/core/testing/testing-sample)
* [Using SQLite to test an EF Core application - Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/testing/sqlite)

## 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
```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
```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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xioc4cu20x6nrrz981ks.png)
I can use "timestamp without time zone", "timestamp with time zone", and so on.

### decimal

I can't sort by decimal properties.

#### Book.cs
```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
```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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uylpixm8wz9h0873hrhu.png)

* [Entity Framework Core in Action, Second Edition](https://www.manning.com/books/entity-framework-core-in-action-second-edition)