Expand tuples to multiple columns

[mqudsi]

It would be awesome if EF Core could expand a (anonymous or named) tuple into multiple columns, assuming the individual fields in the tuple are supported entity types, without needing a custom conversion or mapping property.

e.g.

class Foo {
    int Id {get; set;}
    (int, int) Bar {get; set;}
}

would result in a table [int Id | int Bar_1 | int Bar_2], while a named tuple, e.g.

class Foo {
    int Id {get; set;}
    (int LowerBits, int HigherBits) Bar {get; set;}
}

would result in a table [int Id | int LowerBits | int HigherBits].

Note that this can't (yet) be done via value converters, as there's (currently) no support for mapping one entity to multiple columns.

[roji]

If/when implementing this, don't get in the way of providers which can map tuples natively (the idea is to map this to PostgreSQL composite types).

Note that after discussion in npgsql/npgsql#2097 we decided to drop this idea. C# value tuples don't have names, so any mapping to PostgreSQL composites would rely on field definition ordering, which seems quite dangerous/brittle.

[ajcvickers]

Triage: putting this on the backlog. It's a nice idea that could work with a value converter convention. This would only kick in if the provider doesn't natively support some mapping, so PostgreSQL should be fine. However, it will require #13947 to be implemented first.

[dmitryshunkov]

@roji is there a way to do it with Npgsql currently, as EF Core doesn't support it yet? I have composite primary key (Id, Type) and want to generate next SQL:

SELECT *
FROM Table
WHERE (Id, Type) IN ((1, 1), (2, 1))

It is possible to generate IN clause for one column, using Contains method. But I cannot figure out if it possible for columns within composite type. Maybe there is a workaround?

[roji]

@dmitryshunkov I don't think that's supported at the moment (maybe @smitpatel knows something I don't).

We could make this expressible with the following, assuming blog has a composite key:

var blogs = ctx.Blogs.Where(b => new[]
{
    new Blog { Id = 1, Type = 1 },
    new Blog { Id = 2, Type = 1 }
}.Contains(b)).ToList();

However, please open a separate issue as this is unrelated with the current issue.

[smitpatel]

@roji - Is that valid Sql in postgre? If yes then possibly provider need to override SqlTranslator and put tuple as translated SqlExpression as it can be generated as literal. (Relational one returns null atm)

[dmitryshunkov]

@roji, in my opinion, composite key shouldn't affect the possibility of building query with other columns in row constructor for subquery. Potential syntax could be something like this:

var blogs = ctx.Blogs
   .Where(p => (new[] { new { Id = 1, Type = 1, Title = "New" } })
      .Contains(new { p.Id, p.Type, p.Title }))
   .ToList();

to generate SQL:

SELECT *
FROM Blogs
WHERE (Id, Type, Title) IN ((1, 1, 'New'))

[roji]

@smitpatel yeah, the following works in PostgreSQL, I guess it's yet another provider-specific feature :)

roji=# SELECT 1 WHERE (1, 2) IN ((1, 2), (3, 4));
 ?column? 
----------
        1
(1 row)

roji=# SELECT 1 WHERE (1, 4) IN ((1, 2), (3, 4));
 ?column? 
----------
(0 rows)

Re the syntax, sure that looks good. My proposal above is the "entity equality" version which seems like it could be useful as well.

Opened npgsql/efcore.pg#898 to track.

[marchy]

Just wanted to post an inter-dependency for when this gets taken on: namely that nullable/optional tuples ought to be supported for this.

One of the core benefits of tuples is that they allow encapsulating multiple fields that belong together in a logical unit – thereby instead of modelling them as multiple independent nullable fields in the domain (ie: Field1:T1?, Field2:T2?), you can have a tuple that is nullable itself, but allows the fields to have nullability specified individually (ie: (Field1:T1, Field2:T2)?). For example: a price and its currency – should either neither be set or both be set, but never only one or the other.

Please allow nullable tuples to be modelled when this gets taken on.