.Net core 2.1.
Asset Table -> Id, Name, Path, AssetType(Enum -> user, company), ParentEntityId
I have Asset table that contains media files for multiple other tables( e.g user, companies, departments ). Since asset remain exactly same for all entities it is rational to create it once and define relationships dynamically. So entities are fetched based on AssetType and ParentEntityId, which means its not possible to define relationships for fetching child entities and using default functions like Including and user.Asset wont work.
So my question is
User has images or documents and same goes for companies. Before tables were designed like this.
User -> UserAsset Company -> CompanyAsset
while both UserAsset and CompanyAsset both are replica with only foreign key difference as UserAsset has UserId and CompanyAsset has CompanyId. More entites are coming into system that are going to have same asset requirements. If continue same path there will be 7 Asset tables with exact same data with only difference of Foregin Key. So I thought if I could merge them together in order to avoid having multiple tables. That's why having single asset table makes more sense.
Since asset remain exactly same for all entities it is rational to create it once and define relationships dynamically.
Ah... no. I don't know who designed your database, but it's wrong. Period. That's not a database design to any form of normalization. It does not have foreign keys between entities that are meant to be related.
You should have one asset table. That has an AssetId primary key.
If you want a company to be connected to assets, it can either have an AssetId field foreign key (if it has 0-1 assets) or you can have another table with CompanyId and AssetId as foreign keys (if it has 0-n assets or even n-m when an asset can belong to multiple companies). If users have assets, too, you create a user table just like you did with the company table and reference the same assets table.
With a proper database design, Entity framework will work as desired.