efcore.pg
efcore.pg copied to clipboard
npgsql
Composite support
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?
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.
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).
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.
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.
How is this issue going? I am currently trying to figure out https://github.com/npgsql/npgsql/issues/2154#issuecomment-421465249 this.
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).
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.
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
Is there any plan when 3.0.0 (including support for composite types) could be released?
@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.
@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?
@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, it means PostgreSQL composite types which Npgsql is able to project to CLR types. See test for an example.
@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.
Composite support will be available soon? I see that it has been open since 2016, do you recommend forgetting this and using json?
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 :-) )
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.
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?)
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.
I've moved this to the next milestone, hopefully there'll be enough time to do this.
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
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.
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.
So... how are we meant to handle "complex" data? Without composite types support we are very limited.
@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").
@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.
@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.
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.
