Ability to define custom joins (for readonly navigations)

[domagojmedo]

Not sure if it's currently possible, but would be nice to be able to define a "custom join" maybe.
Say we have a table Metadata and that table has PK Id and columns RowId, TableName and Data.
We also have tables Blog and Post.

In RowId we write down PK of the table with name in TableName. It would then be nice if we could explain to EF how to join from our tables Blog and Post to table Metadata.

If we have List<Metadata> Metadatas in Blog, we should be able to define in ModelBuilder that when we access Blog.Metadatas it would join by Blog.Id = Metadata.RowId and Metadata.TableName = 'Blog'.

I guess there is an issue with what happens if you want to add a Metadata entity to Metadatas list on Blog or Post 🤷

[roji]

@domagojmedo why have TableName on Metadata in the first place? Blog (and presumably Post) already reference a specific Metadata.RowId via the foreign key, what's the purpose of having Metadata.TableName = 'Blog'? Is the idea to allow multiple rows with the same RowId but different TableNames?

[domagojmedo]

Yes yes, RowId isn't really FK, it can reference many tables depending on TableName column

[domagojmedo]

To avoid having N FK columns on the table, 1 for each table that can have a row in metadata table, we have this kind of "soft" foreign key

[roji]

But why not just reference Metadata.Id directly from a Blog.MetadataId foreign key (and similarly from Post.MetadataId)? That allows a very simple, classical one-to-many relationship, where each Blog/Post just references its Metadata row?

[domagojmedo]

But 1 blog can have many Metadata associated with it. It's 1 to N from the other side.

Another example is an audit table where you save a json diff of row when it's updated. You can have many updates for the same row

[MoishyS]

duplicate of #7623?

[AndriySvyryd]

@MoishyS No, while there is some overlap in scenarios the implementation would likely be very different