none
One to Many relations RRS feed

  • Question

  • Hi,

    I have a BlobEntity table that contains paths to files for many other table (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.

    Sunday, March 20, 2016 1:27 PM

Answers

  • You need to rethink the model then. You should not have a base class unless the entity types are closely-related in your domain model.

    It's simply a limitation of the relational model that collections are modeled with foreign keys.  There is no way for an entity to "contain" a collection of other entities.  The many-side of the relationship has to use a foreign key.

    So you can either have separate foreign keys for each relationship, perhaps along with a constraint that exactly one is non-null.  Alternatively you can introduce new linking tables for each relationship.  These would be 1-1 with the Blob table, and 1-many with TableX.

    eg

    One TableX -> Many TableX_BlobTable -> One BlobTable

    Or just have a separate BlobTable for each of TableX, TableY and TableZ, especially if you don't intend for a TableX and TableY to share a single BlobTable row.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, March 21, 2016 2:16 PM
  • To be clear, here are the options (in code-first).

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

    public virtual ICollection<XBlob> Blobs { get; set; } = new HashSet<XBlob>(); } public class XBlob { [Key] [Column(Order = 1)] public int XId { get; set; } [Key] [Column(Order = 2)] public int Id { get; set; } public virtual X X { get; set; } public byte[] BlobData { get; set; } } public class Y { public int Id { get; set; }

    public virtual ICollection<YBlob> Blobs { get; set; } = new HashSet<YBlob>(); } public class YBlob { [Key] [Column(Order = 1)] public int XId { get; set; } [Key] [Column(Order = 2)] public int Id { get; set; } public virtual Y Y { get; set; } public byte[] BlobData { get; set; } }


    and

        public class X
        {
            public int Id { get; set; }
            public int? BlobContainerId { get; set; }
            public virtual BlobContainer BlobContainer { get; set; }
        }
    
    
        public class Y
        {
            public int Id { get; set; }
    
            public int? BlobContainerId { get; set; }
            public virtual BlobContainer BlobContainer { get; set; }
    
        }
    
        public class BlobContainer
        {
            public int Id { get; set; }
            public virtual ICollection<Blob> Blobs { get; set; } = new HashSet<Blob>();
        }
    
        public class Blob
        {
            [Key]
            [Column(Order = 1)]
            public int BlobContainerId { get; set; }
    
            [Key]
            [Column(Order = 2)]
            public int Id { get; set; }
    
            public virtual BlobContainer BlobContainer { get; set; }
            public byte[] BlobData { get; set; }
    
        }

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, March 22, 2016 10:48 AM

All replies

  • Do you intend to have several one-to-many relationships so that a single BlobTable entity is related to zero-or-one tableX entities, and zero-or-one tableY entities?  Because that's what you have declared.   And a single tableX entity may have zero-to-many BlobTable entities, each of which may be shared with a tableY entity, etc.

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

    You may want a many-to-many relationship.  Or give each table a separate BlobTable child.  Or link BlobTable to a common super-type.

    Lots of choices depending on what the domain model wants to be.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, March 20, 2016 2:46 PM
  • Hi David,

    Thanks for your reply.

    You are absolutely right, this is what i'm trying yo achieve. I want one column in BlobEntity that contains the FK for the source tables. this is to correct behavior.

    There is no need here to many to many table as each records in source table could have multiple records in BlobEntity and not the opposite.

    My question is how can i achieve this? how can i create that single column?

    Because adding a list in source tables (in order to create a relation) creates a new column for each one in BlobTable.

    Thanks in advanced.


    Monday, March 21, 2016 10:54 AM
  • You could try something like this:

    namespace EFModel.Model
    {
        using System.Collections.Generic;
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
        using System.Data.Entity;
        class MyDbContext : DbContext
        {
            public DbSet<Customer> Customers { get; set; }
            public DbSet<Person> People { get; set; }
            public DbSet<PersonAddress> Addresses { get; set; }
    
        }
    
        [Table("Person")]
        public class Person
        {
            public int Id { get; set; }
    
            public virtual ICollection<PersonAddress> Addresses { get; set; } = new HashSet<PersonAddress>();
        }
        [Table("Customer")]
        public class Customer : Person
        {
            public string Name { get; set; }
            public string Description { get; set; }
    
    
        }
    
        [Table("Employee")]
        public class Employee : Person
        {
            public string Name { get; set; }
            public string Title { get; set; }
    
        }
    
        public class PersonAddress
        {
            [Key]
            [Column(Order = 1)]
            public int PersonId { get; set; }
            [Key]
            [Column(Order = 2)]
            public int PersonAddressId { get; set; }
            public virtual Person Person { get; set; }
            public string AddressLine1 { get; set; }
            public string AddressLine2 { get; set; }
        }
    }
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, March 21, 2016 1:35 PM
  • David,

    Creating of a base table (according to your suggestion) is best practices and better than keeping multiple FK columns in BlobEntity? keep in mind that in the future, other tables can point the BlobEntity as this table stores files for many functionality in my application.

    Thanks.

    Monday, March 21, 2016 2:04 PM
  • You need to rethink the model then. You should not have a base class unless the entity types are closely-related in your domain model.

    It's simply a limitation of the relational model that collections are modeled with foreign keys.  There is no way for an entity to "contain" a collection of other entities.  The many-side of the relationship has to use a foreign key.

    So you can either have separate foreign keys for each relationship, perhaps along with a constraint that exactly one is non-null.  Alternatively you can introduce new linking tables for each relationship.  These would be 1-1 with the Blob table, and 1-many with TableX.

    eg

    One TableX -> Many TableX_BlobTable -> One BlobTable

    Or just have a separate BlobTable for each of TableX, TableY and TableZ, especially if you don't intend for a TableX and TableY to share a single BlobTable row.

    David


    David http://blogs.msdn.com/b/dbrowne/


    Monday, March 21, 2016 2:16 PM
  • Thank you very much David.

    Appreciate your assistance.

    Monday, March 21, 2016 3:01 PM
  • David,

    Please tell me, Does it make sense to remove the FK restriction (relationship) and create a general column in BlobFiles that will contain all the primary keys of the source tables.

    Is this scenario valid?

    Thanks.

    Monday, March 21, 2016 3:36 PM
  • >Is this scenario valid?

    Not really, no.  It's basically worse than introducing a common super-type.  And you loose DRI and Navigation Properties.

    Another model is you could introduce a BlobContainer entity.

    One TableX -> One BlobContainer -> Many Blobs

    One TableY -> One BlobContainer -> Many Blobs

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, March 21, 2016 3:42 PM
  • I understand all the approaches, but still can't decide which one to go with.

    Considering i have no correlation between the source tables.

    Please correct me if i'm wrong, but the best two seem to be the following:

    1. Create a new linking table between the sources table to one BlobFiles table.

    2. Create a new BlobFiles table for each source table.

    Appreciate your help.


    Monday, March 21, 2016 3:52 PM
  • I agree that those are the best options in this scenario.

    To decide between them, consider whether you have any need to query over all the blobs, or add/delete/change a blob without knowing which entity that blob belongs to.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, March 21, 2016 4:09 PM
  • David,

    Even if i use the linking table approach, i still need to have a linking table for each source table and each linking table will be linked to one BlobFile table, right?
    Is this approach better than creating a BlobFile table for each source?

    Keep in mind that i don't need the records in BlobTable to be in the same place for different source tables. 

    Thanks.
    Tuesday, March 22, 2016 8:35 AM
  • To be clear, here are the options (in code-first).

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

    public virtual ICollection<XBlob> Blobs { get; set; } = new HashSet<XBlob>(); } public class XBlob { [Key] [Column(Order = 1)] public int XId { get; set; } [Key] [Column(Order = 2)] public int Id { get; set; } public virtual X X { get; set; } public byte[] BlobData { get; set; } } public class Y { public int Id { get; set; }

    public virtual ICollection<YBlob> Blobs { get; set; } = new HashSet<YBlob>(); } public class YBlob { [Key] [Column(Order = 1)] public int XId { get; set; } [Key] [Column(Order = 2)] public int Id { get; set; } public virtual Y Y { get; set; } public byte[] BlobData { get; set; } }


    and

        public class X
        {
            public int Id { get; set; }
            public int? BlobContainerId { get; set; }
            public virtual BlobContainer BlobContainer { get; set; }
        }
    
    
        public class Y
        {
            public int Id { get; set; }
    
            public int? BlobContainerId { get; set; }
            public virtual BlobContainer BlobContainer { get; set; }
    
        }
    
        public class BlobContainer
        {
            public int Id { get; set; }
            public virtual ICollection<Blob> Blobs { get; set; } = new HashSet<Blob>();
        }
    
        public class Blob
        {
            [Key]
            [Column(Order = 1)]
            public int BlobContainerId { get; set; }
    
            [Key]
            [Column(Order = 2)]
            public int Id { get; set; }
    
            public virtual BlobContainer BlobContainer { get; set; }
            public byte[] BlobData { get; set; }
    
        }

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, March 22, 2016 10:48 AM
  • Thanks David
    Tuesday, March 22, 2016 1:13 PM
  • Hi David,

    I've a question regarding your solution above.

    In order to fetch the blob, do i have to use "Join" within the link command or i just can use container.blob?

    I'm asking as i get weird errors using container.blob without join.

    Thanks

    Thursday, March 31, 2016 2:10 PM
  • You should almost never need to use join with EF.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, March 31, 2016 3:24 PM