PostgreSQL composite type support

[roji]

In npgsql/npgsql@a9401b1 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 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.

[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 npgsql/npgsql#2154 (comment) 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).

[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)

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. 😄

[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.

[pm64]

Hey @roji, I noticed this was moved from 9.0.0 to Backlog. Does that mean you're potentially planning it for 10.0?

Huge fan, thanks for all you do.

[roji]

That mainly means that it's not going to happen for 9.0, not necessarily that it'll happen in 10. On the EF side, there are plans for improving the complex type mapping support, which may make the possible PG composite type support much better/more feasible.

[ZzZombo]

What's the state of the feature currently? Do we have something to help testing? A quick search for "ComplexType" in the code reveals quite a lot of things, but it's unclear how finished they are.

It's important for me because just the other day I was told on /dotnet/efcore that they are pushing to make complex types supersede owned types, and because I look forward to utilizing JSON columns instead of the many helper tables to store collections of owned (future complex) types.

[roji]

@ZzZombo this isn't implemented yet (open and in the backlog). But it's very important to understand what this is about: it's about allowing to map .NET POCOs to PostgreSQL composite types, which are a PostgreSQL-specific feature involving custom user-defined types in the database. Mapping .NET POCOs to JSON (on PostgreSQL and any other database) is tracked by dotnet/efcore#31252 on the EF side, and has nothing to do with this issue or PostgreSQL composite types (there are pros and cons of mapping to JSON vs. composite types - they're two different mapping strategieS).

In any case, composite mapping is definitely something I want to do, but is far from trivial. However, the work on the EF side to enable complex type JSON mapping should provide some important infrastructure for doing composite mapping here as well.