How do I make SQLite work with a JsonObject field?

Question

We are using EF Core MySQL (Pomelo) for a .NET Core project, and we have a xUnit test setup using an SQLite in-memory database. We're configuring a JSON column that works fine in MySQL but doesn't work in our tests. We've tried adding a ValueConverter, as you can see below, during testing to make SQLite treat the JsonObject as a string instead, but it seems that gets ignored completely. We have also tried to force the column type to "varchar(MAX)".

class FooBar
{
  [Column(TypeName = "json")]
  public JsonObject<Dictionary<string, object>> Foo { get; set; }
}

class FooBarDbContext : DbContext
{
  public DbSet<FooBar> FooBars { get; set; }
  protected override void OnModelCreating(ModelBuilder builder)
  {
    // only during testing.
    builder.Entity<FooBar>().Property(fooBar => fooBar.Foo).HasConversion(
      v => v.ToString(),
      v => new JsonObject<Dictionary<string, object>>(v)).HasColumnType("varchar(MAX)");
  }
}

How do I make SQLite work with a JsonObject field?

Answer 1

Dan, all things considered, my considered recommendation to you would be that you use MySQL for your unit-tests, if that is what you are using in production. Otherwise, you run into several significant dangers:

• The test situation does not match production – MySQL and SQLite are very considerably different in their handling of data types.
• The source-code becomes littered with code that exists only to handle the "impedance mismatch" between the two database types, and between the test and the production situation.
• "What you test," and what tests correctly, "is the test-case code." Problems in the production case therefore slip through and are not unearthed until – (ick ...) – production deployment.

Developers can deploy MySQL servers on their own laptops, with appropriate test data inside, with a server-IP of 127.0.0.1. And so, in this situation, this is what I advise you to do (instead).