Comments (45)
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
from efcore.pg.
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)
internal sealed class CompositeTypeMapping<T> : RelationalTypeMapping
{
private readonly string _typeName;
private readonly Func<T, string[]> _generateSqlFields;
public CompositeTypeMapping(string typeName, Func<T, string[]> generateSqlFields)
: this(typeName, generateSqlFields, new RelationalTypeMappingParameters(new CoreTypeMappingParameters(typeof(T)), typeName))
{
}
private CompositeTypeMapping(string typeName, Func<T, string[]> generateSqlFields, RelationalTypeMappingParameters parameters)
: base(parameters)
{
_typeName = typeName;
_generateSqlFields = generateSqlFields;
}
protected override RelationalTypeMapping Clone(RelationalTypeMappingParameters parameters)
{
return new CompositeTypeMapping<T>(_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<Money>("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. 😄
from efcore.pg.
I've moved this to the next milestone, hopefully there'll be enough time to do this.
from efcore.pg.
@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.
from efcore.pg.
@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.
from efcore.pg.
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.
from efcore.pg.
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.
from efcore.pg.
@ajcvickers, it means PostgreSQL composite types which Npgsql is able to project to CLR types. See test for an example.
from efcore.pg.
@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.
from efcore.pg.
@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.
from efcore.pg.
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).
from efcore.pg.
@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.
from efcore.pg.
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 :-) )
from efcore.pg.
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.
from efcore.pg.
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.
from efcore.pg.
@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.
from efcore.pg.
It's probably a good idea to wait and get some clarity on dotnet/efcore#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.
from efcore.pg.
Looks like ef core has closed #246
from efcore.pg.
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.
from efcore.pg.
How is this issue going? I am currently trying to figure out npgsql/npgsql#2154 (comment) this.
from efcore.pg.
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).
from efcore.pg.
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, npgsql/npgsql#2097). Finally, some EF Core 3.0 work may impact this support as well.
from efcore.pg.
Is there any plan when 3.0.0 (including support for composite types) could be released?
from efcore.pg.
@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?
from efcore.pg.
@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.
from efcore.pg.
@roji Curious--what does "composite" mean in this case?
from efcore.pg.
Composite support will be available soon? I see that it has been open since 2016, do you recommend forgetting this and using json?
from efcore.pg.
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?)
from efcore.pg.
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
from efcore.pg.
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.
from efcore.pg.
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.
from efcore.pg.
So... how are we meant to handle "complex" data? Without composite types support we are very limited.
from efcore.pg.
@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").
from efcore.pg.
@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.
from efcore.pg.
Is this feature still pending? I need to migrate db with complex types.
from efcore.pg.
As you can see from the milestone and from the fact that the issue is open, this hasn't been implemented.
from efcore.pg.
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
from efcore.pg.
it is very sad
from efcore.pg.
https://devblogs.microsoft.com/dotnet/announcing-ef8-rc1/
nothing about composite types. 😒
from efcore.pg.
@roji whats the work around to insert data into a table containing a composite type column using efcore and efcore.npgsql ?
from efcore.pg.
The composite type column can't be mapped by EF, but you can still use raw SQL to interact with it.
from efcore.pg.
can you provide an example ?
from efcore.pg.
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.
from efcore.pg.
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...
from efcore.pg.
@Int32Overflow no, as has been written several times in this issue, the EF provider doesn't currently support composite types.
from efcore.pg.
Related Issues (20)
- Feature Request: protect connection string literal in memory HOT 2
- Support value conversion with generated properties HOT 4
- Running simultaneous queries on different DbContext instances leads to `NpgsqlOperationInProgressException` HOT 3
- Map User Defined Function return table value with parameter timestamp without time zone HOT 3
- Cannot rename system column "xmin" HOT 3
- Reverse engineering for view does not preserve nullability of columns HOT 4
- ISSUE: A PostgreSQL type with the oid '0' was not found in the current database info HOT 1
- SQL translation error when using LEFT JOIN in `Where` HOT 6
- Upgrading INpgsqlArrayConverter to PrimitiveCollection
- EF.Functions support for json_array_length HOT 2
- Npgsql.PostgresException (0x80004005): 42883: function st_contains(deployment.geometry, deployment.geometry) does not exist HOT 4
- ISet<EnumType>.Contains() does not translate in EF Core 8 HOT 1
- EF.Functions.JsonContains() occurs issue after upgrade to .net 8
- Querying a DateTimeOffset in a jsonb as JsonDocument fails with InvalidCastException
- Translation from Contains to strpos leads to extremely slow queries HOT 1
- GroupBy with joined table produces sub optimal query
- Unable to write data to the transport connection: Operation canceled. HOT 7
- Date/time column type mapping to DateTimeOffset / DateOnly / TimeOnly HOT 7
- When upgrading to 8.0.2, using LINQ's [all] and [contains] will cause translation failure. HOT 4
- Is it possible to have a proxy property for json/jsonb field for LINQ-query purposes? HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from efcore.pg.