Entity Framework Code First - One to Many relations from multiple tables

Question

I have a BlobEntity table that contains paths to files for many other tables (tableX, tableY, tableZ, etc...) in my application.

The relation between all the other tables to BlobEntity table is one to many.

Example:

tableX -> BlobTable (OTM)

tableY -> BlobTable (OTM)

tableZ -> BlobTable (OTM)

and the relation is:

public virtual ICollection<BlobEntity> BlobEntity { get; set; }

I'm not sure if this is an issue, but entity framework Code First creates a new FK column in BlobEntity table for each source table.

In my case, BlobEntity contains three FK columns for tableX, tableY and tableZ.

In order to be efficiency, i rather create one column in BlobEntity that contains the FK for the source tables.

Is it reasonable?

Please advise...

Thanks.

Luca Ghersi · Accepted Answer · 2016-03-21T14:37:31.320

2

No, you can't do this even in plain old SQL. You can have a foreing key pointing to more than one table; that's why you need three columns.

If you want to do a "trick" like this, you have to manually manage the relation (I mean, no real FK), but you can't map it into EF.

What about this?

public class EntityA
{
    public int Id { get; set; }

    public int MyFileID {get;set;}
    public virtual MyFiles MyFile { get; set; }
}

public class EntityB
{
    public int Id { get; set; }

    public int MyFileID {get;set;}
    public virtual MyFiles MyFile { get; set; }
}

public class MyFiles
{
    public MyFiles()
    {
        // ReSharper disable once VirtualMemberCallInContructor
        FilesForEntityA = new List<EntityA>();
        // ReSharper disable once VirtualMemberCallInContructor
        FilesForEntityB = new List<EntityB>();
    }


    public int Id { get; set; }

    public int? EntityAId {get;set;}
    public int? EntityBId {get;set;}

    public virtual ICollection<EntityA> FilesForEntityA { get; set; }
    public virtual ICollection<EntityB> FilesForEntityB { get; set; }
}

This way you can have the FK in place and you can easily manager multiple entities. Obviously if you have many files for each entity, you can go with a N-to-N relationship, like this.

edited Mar 21 '16 at 14:37

answered Mar 21 '16 at 11:14

Luca Ghersi

3,261
18
32

I think that a nicer work around could be creating of a base class for my source tables and add the relation to the BlobEntity. what do you think? which choice is better, creating of a base class or keep the BlobEntity with multiple columns? keep in mind that in the future, many table could have OTM relation to this table as BlobEntity stores files for many objects in my application. – user1630359 Mar 21 '16 at 13:54
Sure! But you will have to manage three identities or more, but better than nothing I guess :D You should model around entities and not around blob. I mean, if think you shold have the blobidkey inside your entities table, it's more clear! And also you can have the FK in place :) – Luca Ghersi Mar 21 '16 at 13:56
So, which approach is better? what is the best practices for this scenario? – user1630359 Mar 21 '16 at 13:58
You think that the best practices is to go the opposite way and to store the BlobEntityId in the relevant sources table? in my case is to store the BlobEntityId in tableX/tableY/tableZ accordingly. I does not make sense. in this approach you will have to duplicate the records in source tables as each source table has many record in BlobEntity – user1630359 Mar 21 '16 at 14:35
No! Each table has his own id and you will need to store just the reference id of the blob in each table. I mean, we're saying the same thing :) The only problem is that all entityids have to be nullable, because there is no way to know which one will be the active one. Maybe you can add a string value or a code for that. – Luca Ghersi Mar 21 '16 at 14:36
let's start from beginning. say you have couple of tables which are not related to each other once so ever. each record can have multiple records in BlobFiles. All the primary keys are GUID. what is the best option to add a relation between all the tables to the BlobEntity one? keep in mind that the relation should be OTM. – user1630359 Mar 21 '16 at 14:58
It's exactly what I wrote in the answer; one to many relationships between various entitites and a single BlobFiles table; just switch public int with public Guid. BTW, GUID clustered keys are a big issues in databases, check this: https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega – Luca Ghersi Mar 21 '16 at 15:11
The approach that you wrote creates a column for each source table in BlobFiles table. – user1630359 Mar 21 '16 at 15:31
As you told in the first row of the answer, there is no way of doing this with EF. This is just a workaround. – Luca Ghersi Mar 21 '16 at 15:33

Entity Framework Code First - One to Many relations from multiple tables

1 Answers1