Entity Framework Core - complex type mapped as jsonb column in postgres but mapping fails for InMemory provider

Question

there is a type with complex property as the following:

[Table("contact")]
public class Contact
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("details", TypeName = "jsonb")]
    public Details Details { get; set; }

    ... 
}
    [ComplexType]
    public class Details
    {
        [JsonProperty("firstName")]
        public string FirstName { get; set; }

        [JsonProperty("middleName")]
        public string MiddleName { get; set; }

        [JsonProperty("lastName")]
        public string LastName { get; set; }
    }

that works fine for pgsql provider:

services.AddDbContextPool<ProfileDbContext>((sp, options) =>
            {
                options.UseNpgsql(connStr,
                    x =>
                    {
                        x.EnableRetryOnFailure();
                        x.CommandTimeout(120);
                    });
            });

but when replacing it in TestsStartup with InMemory provider

services.AddDbContextPool<ProfileDbContext>(options =>
            {
                ServiceProvider sp = new ServiceCollection()
                    .AddEntityFrameworkInMemoryDatabase()
                    .BuildServiceProvider();

                options.UseInMemoryDatabase("Profile", _databaseRoot);
                options.UseInternalServiceProvider(sp);
            });

getting an error:

The entity type 'Details' requires a primary key to be defined.If you intended to use a keyless entity type call 'HasNoKey()'.

if I specify through fluent configuration the association OwnsOne it works for InMemory but fails when launching a normal startup with pgsql provider

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Contact>(c => c.OwnsOne(e => e.Details));
}

I suppose due to this jsonb filed type, which is not supported by InMemory provider, is it?

What to do with integration tests, than?

we used in-memory provider for all tests but this jsonb feature seems brokes lifehack with in-memory provider.

Answer 1

In your dbcontext moc:

 public class PostgreSqlDbContextMock : PostgreDbContext
    {
        public PostgreSqlDbContextMock(DbContextOptions<PostgreSqlDbContextMock> options) : base(options)
        {
        }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseInMemoryDatabase(Guid.NewGuid().ToString());
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<EntityWithJsonbProperty>().Property(x => x.SessionData).HasConversion(
                v => JsonConvert.SerializeObject(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }),
                v => JsonConvert.DeserializeObject<YouJsonbProperty>(v, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }));
        }
    }

Answer 2

InMemory is a very limited solution for testing, since it cannot actually do many things that real databases can - transactions, raw SQL, and in this case the jsonb type.

For full integration tests, the general recommendation is to use your actual production database, giving you the best test fidelity - the EF Core docs provide detail on this. Otherwise, for unit tests you can have a repository abstraction and mock that, in which case you don't need to use EF Core at all.