Composite support

[roji]

In https://github.com/npgsql/npgsql/commit/a9401b13baf800d8ac600ce5ca60adc371b9633d I added composite type support, but since then EFCore changed things in a way that breaks it. It appears that the composite type is now picked up as a navigation property - this makes sense but find a way to make EFCore treat it as scalar?

[roji]

It's probably a good idea to wait and get some clarity on https://github.com/aspnet/EntityFramework/issues/246 before implementing this. Otherwise we may end up with something in Npgsql that isn't compatible with whatever the EF team decide to do with complex/value objects.

[roji]

Note: in addition to the actual mapping of the composite type, there's the question of support for creating the PostgreSQL type (i.e. CREATE TYPE).

[xumix]

Looks like ef core has closed #246

[roji]

Looking again, I don't see any reason not to implement composite support, which should be somewhat similar to the enum support added to 2.1.

[roji]

Note: the implementation should be very similar to what we've done for enums. However, keep in mind the idea of accessing unmapped composites as value tuples (or possible mapping composites to value tuples) at the ADO.NET level.

[smblee]

How is this issue going? I am currently trying to figure out https://github.com/npgsql/npgsql/issues/2154#issuecomment-421465249 this.

[roji]

This is one of the main features planned for 2.2, but that's a few months away. Note that you can already use composite types when working at the ADO.NET level (not EF Core).

[roji]

Am punting this for 3.0. This feature is quite big/complex, and we're pretty close to shipping 2.2. There's also an interaction with the possible work to allow mapping composites to value tuples (not yet done at the ADO level, https://github.com/npgsql/npgsql/issues/2097). Finally, some EF Core 3.0 work may impact this support as well.

[roji]

Note: we should be looking at supporting composite via the EF Core owned entity support. EF Core already allows owned entities to be represented as either columns in the principal table (aka table splitting), or via a separate table (by specifying ToTable()). Moreover, if I understand correctly, the intention is for document databases (e.g. Cosmos) to handle owned entities by nesting them in the document. PostgreSQL composites could fit very nicely in this schema - we should investigate this.

/cc @divega @ajcvickers

[dorny]

Is there any plan when 3.0.0 (including support for composite types) could be released?

[roji]

@dorny at this point it's unlikely that 3.0.0 will contain composite support... But note that it does include some powerful new JSON mapping capabilities, which could cover similar cases.

[dorny]

@roji may I ask you for an advice? We have to provide IQueryable (for OData endpoint) where data are stored using custom types (nested composite types, including arrays). We can basically do two things - try to extend EF+Npgsql to support this or create own "mini-ORM" solution for our limited use-case (known data model, no SQL JOINS, etc.). With your experience - what would you suggest?

[roji]

@dorny if you absolutely must represent data in the database using composite types (as opposed to a more traditional relational representation, then you'll need specific support in EF Core do this (that's what this issue is about); I doubt there's any way around that. The new JSON support provides something very similar - you can store a POCO recursively (including arrays) in a single column - the only difference is the column's type (jsonb vs. a composite type). To JSON might be a good way to go.

Note that simply mapping composite types for saving and materialization is only part of the problem (and not too hard) - proper support would also involve traversal of properties inside the composite handler (again, see what's already possible with JSON on this).

Note that Npgsql does fully support composite types at the ADO layer - the EF Core support is what's missing.

[ajcvickers]

@roji Curious--what does "composite" mean in this case?

[YohDeadfall]

@ajcvickers, it means PostgreSQL composite types which Npgsql is able to project to CLR types. See test for an example.

[roji]

@ajcvickers composite types are basically PostgreSQL user-defined types. You can create an arbitrary new store type in PG (CREATE TYPE ...) and use that anywhere you would use a regular, built-in type (in columns, in other composite types...).

Some comparison points:

• Composite has an efficient binary wire encoding, unlike the JSON types which (currently!) transfer in text, and so require slower string-based serialization/deserialization.
• JSON is considerably richer in terms of terms of searching/querying operators/functions (docs).
• Composite can contain any other PG type, including recursion, whereas the JSON type system is limited. This may not be very important in practice as JSON serialization can convert any CLR object to JSON object, etc.
• There may be differences in indexing capabilities (i.e. index based on a property within JSON/composite).
• In general JSON has received much more attention recently.

[rrodriguezreyes]

Composite support will be available soon? I see that it has been open since 2016, do you recommend forgetting this and using json?

[markusschaber]

My gut feeling tells me: If you don't have any reasons to prefer composite types (e. G. an existing database schema shared with other applications, or one of the cases mentioned by @roji ), I'd go for JSON for new developments. (But that's just my gut feeling as a long time PostgreSQL user :-) )

[roji]

Yeah, I'd recommend trying out JSON instead - it's definitely more modern and receiving more general attention. Otherwise if there's enough request for composite mappings I'll do that as well at some point.

[markusschaber]

Hmm, are there any plans for a more efficient binary wire encoding for JSON on the PostgreSQL side? (I guess this could be based on the composite type encoding, or external specs like MessagePack or CBOR?)

[dorny]

While both JSON and composite types can be used to store document-like objects in DB columns, each approach has different pros and cons. With JSON you have no fixed schema - if this is good or bad depends on specific use case. If you have lot of numbers or binary data in your document, then you get significantly better performance with composites. JSON/JSONB is slower because data are transmitted as strings and there is more work on database to parse and store it.

It would be nice to have composites supported in EF. There are some use cases where JSON/JSONB is not acceptable solution.

[roji]

I've moved this to the next milestone, hopefully there'll be enough time to do this.

[rrodriguezreyes]

Yes, I was thinking about the performance of a composite vs json.

But reading the recommendations at the moment I will have to use json, but it would be good to have the composite available for some specific cases.

Thanks for the feedback

[andrei9669]

Can someone please help. I have an existing DB that uses composites. How could I map them? Lets say I have table "vehicle" and in it, there is a composite "measurements" that has length, width and height.

[roji]

If you're referring to the PostgreSQL-specific composite type feature (CREATE TYPE complex AS (r double precision, i double precision)), then that's not supported by the EF Core provider. For now, you can only use composites in raw SQL with ADO.NET.

[Akronae]

So... how are we meant to handle "complex" data? Without composite types support we are very limited.

[roji]

@Akronae for one thing, you can map objects to PostgreSQL jsonb, which can be considered as an alternative to PostgreSQL composite types in at least some scenarios. Check out the provider's JSON mapping capabilities.

Aside from that, EF Core also the concept of owned entity types which can frequently be a good fit. For example, you can map your complex type to columns in the same database row as the owning entity's ("table splitting").

[Akronae]

@roji I did not know about JSON POCO mapping, the major downside is that it isn't supported by Scaffold-DbContext (?), so from now on we'll have to write models manually. Thanks it helps.

[roji]

@Akronae yeah. As a general rule, EF will not scaffold non-entity types out of the database - this is true for enums, jsonb, and also for composites once support for that is done.

However, scaffolding isn't an all-or-nothing thing. You can use partial classes to continue scaffolding from PG, but implement your jsonb POCOs manually.

[davidkudera]

Yeah, I'd recommend trying out JSON instead - it's definitely more modern and receiving more general attention. Otherwise if there's enough request for composite mappings I'll do that as well at some point.

I'm here only to increase the "requests counter" after our failed attempt to use EF with quite a big existing database. There are a few composite types that are used at practically every table multiple times. Also, all of them have custom domain checks too (CREATE DOMAIN ... AS ... CHECK (...)) so switching to owned entity types or JSON is not so easy for us.

[davidkudera]

So... I didn't want to be defeated by this issue so I come up with a workaround - custom RelationalTypeMapping.

Don't look, don't use, it's probably a really bad implementation... (click at your own risk)

```c# internal sealed class CompositeTypeMapping : RelationalTypeMapping { private readonly string _typeName; private readonly Func _generateSqlFields; public CompositeTypeMapping(string typeName, Func generateSqlFields) : this(typeName, generateSqlFields, new RelationalTypeMappingParameters(new CoreTypeMappingParameters(typeof(T)), typeName)) { } private CompositeTypeMapping(string typeName, Func generateSqlFields, RelationalTypeMappingParameters parameters) : base(parameters) { _typeName = typeName; _generateSqlFields = generateSqlFields; } protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters) { return new CompositeTypeMapping(_typeName, _generateSqlFields, parameters); } protected override string GenerateNonNullSqlLiteral(object value) { return "(" + string.Join(',', _generateSqlFields((T)value)) + ")::" + _typeName; } } internal sealed class CompositeTypesTypeMappingSourcePlugin : IRelationalTypeMappingSourcePlugin { public RelationalTypeMapping? FindMapping(in RelationalTypeMappingInfo mappingInfo) { if (mappingInfo.ClrType == typeof(Money)) { return new CompositeTypeMapping("coin", type => new[] { type.Amount.ToString(), type.Currency.Code, }); } return null; } } ```

Right now we only need to have mapping working and surprisingly my CompositeTypeMapping meets this requirement.

But it doesn't support migrations (we don't use EF for that right now) and non-trivial conditions don't work as well:

• This works: Set.Where(x => x.Price == new Money(4200, "EUR"))
• But this doesn't: Set.Where(x => x.Price.Amount == 4200)

I tried to implement a custom IMemberTranslatorPlugin to translate the example code to (x.price).amount = 4200 but then I needed to create (I think) custom CompositeTypeFieldAccessExpression : SqlExpression and for that I needed custom IRelationalParameterBasedSqlProcessorFactory because the default SqlNullabilityProcessor.VisitCustomSqlExpression was not happy. Unfortunately, my IRelationalParameterBasedSqlProcessorFactory was not working and I didn't want to spend any more time on it, because I can still use raw SQL.

---

Anyway, I'm hopeful this issue will get resolved one day so I can get rid of the code I just wrote. 😄

[roji]

@davidkudera yep, just mapping a composite type property is easy enough, but making the whole thing work with the query pipeline, migrations etc. is another thing entirely.

One more important note... Mapping composite via a type mapping means that the contents of the composite generally opaque to EF Core; this means that it would be impossible to implement partial updates, where EF Core can send SQL to only change a single property within a composite value. This is roughly how JSON mapping is implemented right now (therefore no partial updates there either), but on the EF Core side JSON support is going to implemented with owned entities, which will allow partial updates. Once that happens on the EF side, I'll likely look at doing it for PostgreSQL both for JSON and for composite.

[onurkanbakirci]

Is this feature still pending? I need to migrate db with complex types.

[roji]

As you can see from the milestone and from the fact that the issue is open, this hasn't been implemented.

[GeXiaoguo]

I am confused on the composite type support as well. Looking at this page, it seems to be clear that it is supported. But reading this thread, it is clearly not. b.t.w I've followed the document above trying to map a composite type but got this error could not be mapped because the database provider does not support this type

[roji]

@GeXiaoguo that page points to the low-level, ADO.NET Npgsql driver, where composite types are indeed supported; however, the EF Core provider does not support them. These are two separate components (with the EF Core provider built on top of the ADO.NET driver), and support in them means very different things.

[RomanSoloweow]

it is very sad

[EzequiasLiborio]

https://devblogs.microsoft.com/dotnet/announcing-ef8-rc1/ nothing about composite types. 😒

[roji]

@EzequiasLiborio that's right - composite types won't be supported in 8.0, there simply wasn't enough time with everything else. However, EF Core 8.0 does include support for complex types, which are an important infrastructure that composite types will be built on top of. So we're going in the right direction for that.

[Anand-Chourasia]

@roji whats the work around to insert data into a table containing a composite type column using efcore and efcore.npgsql ?

[roji]

The composite type column can't be mapped by EF, but you can still use raw SQL to interact with it.

[Anand-Chourasia]

can you provide an example ?

[roji]

Using SQL to insert data via SQL is covered on this doc page; for the specific SQL take a look at the PG documentation. If you're having trouble, post a question on stackoverflow asking for assistance.

[Int32Overflow]

Is there a good workaround? I would like to integrate an old existing database with composites into the EntityFrameworkCore. I don't need a migration, just Insert/Update/Delete/Where...

[roji]

@Int32Overflow no, as has been written several times in this issue, the EF provider doesn't currently support composite types.