JSONB column not mapped usign DB First approach

[tonykaralis]

Hi,

I have a very simple database with a table containing a few columns. All but one of the columns are appearing in the table within the .edmx designer.

I have successfully installed EF6 and NpgSql as well as the VSIX extension to help with reverse engineering the database.

Everything has worked fine except the jsonbcolumn is not mapped.

Strange thing is looking at the database in server explorer shows the column.

Is this a known limitation? Are jsonb columns not recognizable? I am not able to find anything stating how to work around this, or anyone who has had this problem.

Thanks

[tonykaralis]

Hi @roji , I have managed to get this to work with EF Core. Do you have any info regarding the above issue?

[roji]

Unfortunately EF6's support for column types is a bit limited (unlike EF Core), so any special types beyond the standard ones aren't well-supported. It may definitely be possible to support jsonb in database first - this may be a simple matter of adding it to https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/Resources/NpgsqlProviderManifest.Manifest.xml, but I'm not going to have any time to look at this in the near future. If anyone wants to try this that would be great.

[tonykaralis]

Thanks for getting back to me on this.

[meoblast001]

I'm guessing this issue is related to #99?

[marcfal]

Hi @roji ,

I have tried to make json and jsonb work with EF6 and database-first approach. So far I have made the following changes to https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/Resources/NpgsqlProviderManifest.Manifest.xml

<Type Name="json" PrimitiveTypeKind="String">
      <FacetDescriptions>
        <FixedLength DefaultValue="false" Constant="true" />
        <!-- TODO: Need a good value for MaxLength -->
        <MaxLength DefaultValue="1073741823" Constant="true" />
        <Unicode DefaultValue="true" Constant="true" />
      </FacetDescriptions>
</Type>

<Type Name="jsonb" PrimitiveTypeKind="String">
  <FacetDescriptions>
    <FixedLength DefaultValue="false" Constant="true" />
    <!-- TODO: Need a good value for MaxLength -->
    <MaxLength DefaultValue="1073741823" Constant="true" />
    <Unicode DefaultValue="true" Constant="true" />
  </FacetDescriptions>
</Type>

And in https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/NpgsqlProviderManifest.cs line 155

case "text":
case "xml":
case "json":    // Added
case "jsonb":  // Added1
    return TypeUsage.CreateStringTypeUsage(primitiveType, isUnicode, false);

Simple inserts/select from tables with a json/jsonb field work fine. However I'm facing an issue for more complex requests needing UNION and CAST. For example if I do the following :

DataContext
.tableWithoutJson
.Include(tableWithoutJson_item=> tableWithoutJson_item.relatedTableWithJson)

I get the following error :

Npgsql.PostgresException (0x80004005): 42804: UNION types text and jsonb cannot be matched

I inspected the generated DB query and found that the JSONB column "NULL" value for UNION is casted as TEXT. I did dig into https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/SqlGenerators/SqlBaseGenerator.cs

And found at line 803 (GetDbType method) :

case PrimitiveTypeKind.String:
    return "text";

used in https://github.com/npgsql/EntityFramework6.Npgsql/blob/dev/src/EntityFramework6.Npgsql/SqlGenerators/SqlSelectGenerator.cs line 107

// must provide a NULL of the correct type
// this is necessary for certain types of union queries.
public override VisitedExpression Visit(DbNullExpression expression)
            => new CastExpression(new LiteralExpression("NULL"), 
                  GetDbType(expression.ResultType.EdmType));

which I think is the culprit. However I don't know how to distinguish the case of JSON/JSONB values with only the EdmType at disposal.

I'm not very familiar with EntityFramework or Postgres so please correct me if I'm wrong. Any help would be appreciated. Maybe there is a way to make Postrgres tolerate this ?

Thanks