Npgsql / EF 6 - json column

Question

I'm trying to create a migration with a JSON column. Here's what I tried:

    [Column(TypeName = "Jsonb")]
    public string Data { get; set; }

    [Column(TypeName = "Json")]
    public string Data { get; set; }

    modelBuilder.Entity<Member>().Property(p => p.Data).HasColumnType("Json");

    modelBuilder.Entity<Member>().Property(p => p.Data).HasColumnType("Jsonb");

Nothing works at all, here's the exception:

System.InvalidOperationException: Sequence contains no matching element at System.Linq.Enumerable.Single[TSource](IEnumerable1 source, Func2 predicate) at System.Data.Entity.Utilities.DbProviderManifestExtensions.GetStoreTypeFromName(DbProviderManifest providerManifest, String name) at System.Data.Entity.ModelConfiguration.Configuration.Properties.Primitive.PrimitivePropertyConfiguration.ConfigureColumn(EdmProperty column, EntityType table, DbProviderManifest providerManifest) at System.Data.Entity.ModelConfiguration.Configuration.Properties.Primitive.PrimitivePropertyConfiguration.Configure(EdmProperty column, EntityType table, DbProviderManifest providerManifest, Boolean allowOverride, Boolean fillFromExistingConfiguration) at System.Data.Entity.ModelConfiguration.Configuration.Properties.Primitive.PrimitivePropertyConfiguration.<>c__DisplayClass4.b__3(Tuple2 pm) at System.Data.Entity.Utilities.IEnumerableExtensions.Each[T](IEnumerable1 ts, Action1 action) at System.Data.Entity.ModelConfiguration.Configuration.Properties.Primitive.PrimitivePropertyConfiguration.Configure(IEnumerable1 propertyMappings, DbProviderManifest providerManifest, Boolean allowOverride, Boolean fillFromExistingConfiguration) at System.Data.Entity.ModelConfiguration.Configuration.Types.StructuralTypeConfiguration.ConfigurePropertyMappings(IList1 propertyMappings, DbProviderManifest providerManifest, Boolean allowOverride) at System.Data.Entity.ModelConfiguration.Configuration.Types.EntityTypeConfiguration.ConfigurePropertyMappings(DbDatabaseMapping databaseMapping, EntityType entityType, DbProviderManifest providerManifest, Boolean allowOverride) at System.Data.Entity.ModelConfiguration.Configuration.Types.EntityTypeConfiguration.Configure(EntityType entityType, DbDatabaseMapping databaseMapping, DbProviderManifest providerManifest) at System.Data.Entity.ModelConfiguration.Configuration.ModelConfiguration.ConfigureEntityTypes(DbDatabaseMapping databaseMapping, ICollection1 entitySets, DbProviderManifest providerManifest) at System.Data.Entity.ModelConfiguration.Configuration.ModelConfiguration.Configure(DbDatabaseMapping databaseMapping, DbProviderManifest providerManifest) at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo) at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection) at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext) at System.Data.Entity.Internal.RetryLazy2.GetValue(TInput input) at System.Data.Entity.Internal.LazyInternalContext.InitializeContext() at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) at System.Data.Entity.Internal.Linq.InternalSet1.Initialize() at System.Data.Entity.Internal.Linq.InternalSet1.get_InternalContext() at System.Data.Entity.Internal.Linq.InternalSet1.get_Local() at System.Data.Entity.DbSet`1.get_Local() at System.Data.Entity.DbModelBuilderExtensions.RegisterUserAccountChildTablesForDelete[TKey,TAccount,TUserClaim,TLinkedAccount,TLinkedAccountClaim,TPasswordResetSecret,TTwoFactorAuthToken,TUserCertificate](DbContext ctx) in c:\ballen\github\brockallen\BrockAllen.MembershipReboot\src\BrockAllen.MembershipReboot.Ef\DbModelBuilderExtensions.cs:line 26

Here's my config:

<package id="EntityFramework" version="6.1.1" targetFramework="net452" />
<package id="EntityFramework6.Npgsql" version="3.1.1" targetFramework="net452" />
<package id="Npgsql" version="3.1.6" targetFramework="net452" />

Answer 1

So, this is totally doable, but requires modifying the generated migration rather than annotating your model or using fluent configuration. In your generated migration, alter the declaration of the data column to use storeType and defaultValueSql arguments:

data = c.String(nullable: false, storeType: "jsonb", defaultValueSql: "'{}'::jsonb")

I can confirm this works for Npgsql 3.1.7 with EntityFramework6.Npgsql 3.1.1, and will save and load applicable entities without issue. Can't vouch for earlier versions.