0

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; }
}
DavidG
  • 113,891
  • 12
  • 217
  • 223
Sergio
  • 1,383
  • 2
  • 13
  • 28
  • 1
    Add `public Attachment Attachment { get; set;}` to your class, what is confusing you? – DavidG Mar 24 '17 at 13:41
  • You mean `public virtual ICollection Attachments { get; set; }` ? The problem with that is that it doesn't join the tables by "ParentId" and then it does create a CustomerId, ProductId... one field for each linked table, in the attachments table. – Sergio Mar 24 '17 at 13:52
  • Being a guid unique across tables, should be a way for resolving the relation with just one field in the destination table. Or I'm wrong? – Sergio Mar 24 '17 at 13:54

1 Answers1

0

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

Cons

handling the GUID sequence which must be unique for all tables

Community
  • 1
  • 1
Modar Na
  • 873
  • 7
  • 18