JsonElement property can't be mapped in EF Core with Sqlite

Question

I'm trying to use Sqlite in memory database for running unit tests on my Entity Framework Core 5 based project but I'm getting an error when initialising the DbContext that doesn't occur with either the production Npgsql Postgres driver or EF Cores own in memory database.

System.InvalidOperationException: 'The property 'AlertDefinition.Parameters' could not be mapped because it is of type 'Nullable', which is not a supported primitive type or a valid entity type. Either explicitly map this property, or ignore it using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.'

I'm not sure how to solve this without affecting the real Postgres database functionality, which works as I wish.

The relevant entity classes

public class AlertDefinition
{
    [Required]
    public JsonElement? Parameters { get; set; }

    public List<AlertOutput>? Outputs { get; set; }
}

public class Rule
{
    public Guid? Id { get; set; }

    [StringLength(254)]
    public string Name { get; set; } = string.Empty;

    [Required]
    public AlertDefinition AlertDefinition { get; set; } = null!;
}

public class RulesDbContext : DbContext
{
    public DbSet<Rule> Rules { get; set; } = null!;

    public DbSet<AlertOutput> AlertOutputs { get; set; } = null!;

    public RulesDbContext(DbContextOptions options)
        : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Define AlertDefinition as an owned entity
        modelBuilder.Entity<Rule>()
            .OwnsOne(r => r.AlertDefinition, adBuilder =>
            {
                adBuilder.OwnsMany(ad => ad.Outputs, oBuilder =>
                {
                    oBuilder.HasKey(o => o.Id);
                    oBuilder.ToTable("alert_outputs");
                });
            });
    }
}

I was previously using EF Core in memory db but this can't be used going forward as I'm now using features that it doesn't support and using Postgres for the unit tests would be difficult, if not impossible on our CI which needs to run the unit tests.

So, why do not start Postgres and test against real database? You will face a lot of other differences between providers. Nice reading about integration tests [here](https://khalidabuhakmeh.com/dotnet-database-integration-tests) — Svyatoslav Danyliv, Nov 26 '21 at 11:39
As I mentioned in the question, Postgres would be difficult to use from our CI server infrastructure and also we have multiple developers (scattered around the world) who don't have a local postgres installation. We use a docker container for running the applicaiton locally, which isn't neccessarily running when executing unit tests and getting a proper installation on each dev machine would be difficult (and many of them are very restricted in RAM so don't want it running all the time). Being able to use a simple in memory db should be sufficient for our needs if I can find a solution to this — Mog0, Nov 26 '21 at 11:46

score 0 · Answer 1 · answered Aug 19 '22 at 13:20

It's a bad practice but it may be pragmatic in some cases:

modelBuilder.Entity<Rule>(entity =>
            {
                if(UnitTestDetector.IsRunningFromNUnit)
                {
                    //unit test mapping
                   entity.Ignore(e => e.Parameters);
                }
                else { "your default mapping" }

UnitTestDetector.IsRunningFromNUnit assuming NUnit

JsonElement property can't be mapped in EF Core with Sqlite

1 Answers1