How can a common "Attachments" table can be linked to several tables in Entity Framework Core?

Question

I have several entities that I want to give the possibility to store files. Like for a given customer, storing a copy of it's identity card or for a product, storing it's import documents and also quality certificates.

I don't want those files to be stored in the same entities rows and I've created a separate table for attachments. Decoupling it also gives the opportunity to later decide if I want to directly store the file, the storage location or some CMS access token.

So how I can make in EF for having such relationship between this attachments table and all the other tables that may have that functionalty?

An schema sample:

public class Customer
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Product
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Attachment
{
    [Key]
    public int Id { get; set; }
    //Relation property
    public int ParentId { get; set; }
    public string Name { get; set; }
    public byte[] Content { get; set; }
}

Answer 1

there are different possibilities to consider

Method 1

make the attachment table to store the table name along with the id

then when you get some object Product check if it has an attachment using ProdcutId with the table name which is can be accessible using reflection

Pros:

• you get rid of the GUID which causes some toubles

• you have a simple table structure with only Id RefrencedTable RefrencedId

Cons

Accessing the Attachment list would perform a full table scan (you can minimize this impact using indexes) still you need to compare with the string RefrencedTable

Method 2

defining an Attachments list in all needed tables which will create a foriegn key for each table

Pros

• easier access to the Attachments list from any other object

• still no Guid

Cons

possibly very large table with a lot of nullables and indexes

Method 3

using shared GUID

Pros

• simple structure

• one index (check more about clustered index)

Cons

handling the GUID sequence which must be unique for all tables