Add support for collections of primitive types as separate table in relational databases

[qetza]

Currently it's not possible to store collections of primitive types in their own table so that we can easily query on those values. All proposed solutions are to serialize the collection but this does not allow for querying it's content easily or to create a class with a single property to represent the new table.

Through configuration it would be great to be able to define a table assigned to the collection so that it can be stored in it's own table with a foreign key referencing the parent entity. This could be configured like a collection of owned entities stored in it's own table where the value of the collection is the only column (with the foreign key).

So the entity:

public class Role
{
  public string Id { get; set; }
  public string Name { get; set; }
  public ICollection<string> Members { get; set; }
}

with the following configuration:

modelBuilder.Entity<Role>().OwnsMany(
    r => r.Members, 
    b => {
        b.ToTable("RoleMembers");
        b.WithOwner().HasForeignKey("RoleId");
        b.Property(b => b).HasColumnName("Name");
        b.HasKey("RoleId", "Name");
    });

would be persisted in 2 tables:

• Roles
  • Id
  • Name
• RoleMembers
  • RoleId
  • Name

[AndriySvyryd]

Part of #4179

[ajcvickers]

Contrast to #29427.

Note from triage: also consider other issues related to primitive collections--see label area-primitive-collections.

[roji]

[ajcvickers]

@roji Did you see this label?

(We needed a label for things we needed to talk to you about when you got back...)

[roji]

Of course I saw it :) And I thought it was only a mechanism for trolling me :rofl:

[ajcvickers]

Of course I saw it :) And I thought it was only a mechanism for trolling me 🤣

Well, that was the primary concern.