locked
Table Splitting Entity Framework Code First - 3+ Entities RRS feed

  • Question

  • User-1134857695 posted

    First of all, is it possible? 

    Hi everyone! For the first time ever I bump into the following problem with Entity Framework (6.1) and its Code First approach: 

    (Can read more in depth in here: http://programmers.stackexchange.com/questions/315857/entity-framework-code-first-c-class-separation-and-eav)

    Summarizing the link above: I have a table in which I've approached as OOP in EF Code First. The problem is that this has created an Entity-Attribute-Value approach in the Database. 

    I later learned about Table Splitting, which would allow me to map 2 entities to a same table. This can solve my problem, the thing is that I have no found an example online that shows me how to do this with 3 or more entities... and I'm stuck. Does anyone know how to do this? 

    This are my errors:

    One or more validation errors were detected during model generation:
    
    Pro_Gaming.Infrastructure.IdentityUserLogin: : EntityType 'IdentityUserLogin' has no key defined. Define the key for this EntityType.
    Pro_Gaming.Infrastructure.IdentityUserRole: : EntityType 'IdentityUserRole' has no key defined. Define the key for this EntityType.
    IdentityUserLogins: EntityType: EntitySet 'IdentityUserLogins' is based on type 'IdentityUserLogin' that has no keys defined.
    IdentityUserRoles: EntityType: EntitySet 'IdentityUserRoles' is based on type 'IdentityUserRole' that has no keys defined.

    Here I show you the model. All properties that have virtual and are not contained in a  "List", are meant to be mapped into the same table. I have tried from using the Fluent API (which I think I've missed) to using Data Annotations in the models. 

    I have also shared this answer in Stack Overflow with no answers:

    [Table("Tournament")]
        public partial class PGTournament : IImageable
        {
    
            public int Id { get; set; }
            public string Name { get; set; }
            public GameGenre GameGenre { get; set; }
            public TournamentFormat TournamentFormat { get; set; }
    
            public TournamentStatus Status { get; set; } 
            public string Description { get; set; }
            public virtual List<PrizePool> Prizes { get; set; }
    
            public DateTime StartDate { get; set; }
            public DateTime EndDate { get; set; }
            public virtual List<Participants.Participant> Participants { get; set; }
            public decimal Cost { get; set; }
            public string Streaming { get; set; }
            public int? ChallongeTournamentId { get; set; }
            public string Bracket { get; set; }
            public virtual List<TournamentMatch> Matches { get; set; }
            public int MainImageId { get; set; }
            public virtual Media MainImage { get; set; }
            public bool IsFollowUp { get; set; }
            public int? FollowUpTournamentId { get; set; }
            [ForeignKey("FollowUpTournamentId")]
            public virtual PGTournament FollowUptournament { get; set; }
            public int MediaID { get; set; }
            public int MainImageID { get; set; }
    
            //Properties that share same table:
            public virtual TournamentOrganizer Organizer { get; set; } //Change to Organizer
            public virtual TournamentOrganizerSetting OrganizerSetting { get; set; }
            public virtual TournamentSettings TournamentSettings { get; set; }
            public virtual TournamentRules Rules { get; set; }
    
    
        }

    [Table("Tournament")]
        public partial class TournamentOrganizer
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string UserId { get; set; }
            [ForeignKey("UserId")]
            public AppUser User { get; set; }
    
            public int LogoId { get; set; }
            [ForeignKey("LogoId")]
            public Media Logo { get; set; }
            public virtual TournamentOrganizerSetting Settings { get; set; }
            public virtual TournamentRules Rules { get; set; }
            public virtual TournamentSettings TournamentSettings { get; set; }
            public virtual PGTournament Tournament { get; set; }
    
    
        }
    
    [Table("Tournament")]
        public partial class TournamentSettings
        {
            public int Id { get; set; }
            public string Address { get; set; }
            public string LocationGoogleMaps { get; set; }
            public bool isOnline { get; set; }
            public int MaxPlayers { get; set; }
            public List<TournamentAssistant> TournamentAssistants { get; set; }
    
            public virtual TournamentOrganizer Organizer { get; set; } //Change to Organizer
            public virtual TournamentRules Rules { get; set; }
            public virtual TournamentOrganizerSetting OrganizerSettings { get; set; }
            public virtual PGTournament Tournament { get; set; }
    
        }
    
     [Table("Tournament")]
        public partial class TournamentOrganizerSetting
        {
            public int Id { get; set; }
            public string Location { get; set; }
            //Properties that share same table:
            public virtual TournamentOrganizer Organizer { get; set; } //Change to Organizer
            public virtual TournamentRules Rules { get; set; }
            public virtual TournamentSettings TournamentSettings { get; set; }
            public virtual PGTournament Tournament { get; set; }
        }
    
     [Table("Tournament")]
        public partial class TournamentRules
        {
            public int Id { get; set; }
            public string Bans { get; set; }
            public string Allowed { get; set; }
            public string Description { get; set; }
            public string FileName { get; set; }
            public string FilePath { get; set; }
    
            //Properties that share same table:
            public virtual TournamentOrganizer Organizer { get; set; } //Change to Organizer
            public virtual TournamentOrganizerSetting OrganizerSetting { get; set; }
            public virtual TournamentSettings TournamentSettings { get; set; }
            public virtual PGTournament Tournament { get; set; }
        }

    This is my approach with the Fluent API

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {    
                modelBuilder.Entity<PGTournament>().ToTable("Tournament");
    
    
                modelBuilder.Entity<PGTournament>()
                    .HasKey(e => e.Id)
                    .HasOptional(e => e.FollowUptournament)
                    .WithMany();
    
                modelBuilder.Entity<PGTournament>()
                    .HasKey(e => e.Id)
                    .HasRequired(e => e.Organizer)
                    .WithRequiredDependent(e => e.Organizer)
    
                modelBuilder.Entity<TournamentOrganizer>()
                    .HasKey(e => e.Id)
                    .HasRequired(e => e.Settings)
                    .WithRequiredDependent(e => e.Organizer);
    
                modelBuilder.Entity<TournamentViewModel>()
                    .HasKey(e => e.Id)
                    .HasRequired(e => e.Settings)
                    .WithRequiredDependent(e => e.Organizer);
    
    
                modelBuilder.Entity<TournamentOrganizer>().Map(m => m.ToTable("Tournament"));
                modelBuilder.Entity<TournamentOrganizerSetting>().Map(m => m.ToTable("Tournament"));
    
                base.OnModelCreating(modelBuilder);
    
            }

    I have posted this problem in Stack Overflow with no answers so far:

    http://stackoverflow.com/questions/36670709/table-splitting-entity-framework-code-first-3-entities 

    Tuesday, April 19, 2016 12:56 AM

Answers

  • User1559292362 posted

    Hi superjose,

    First of all, is it possible? 

    Yes, It's possible, and I create a demo as below for your reference.

    model.cs

    public class Employee
        {
            public int EmployeeID { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string Title { get; set; }
            public string TitleOfCourtesy { get; set; }
            public DateTime? BirthDate { get; set; }
            public DateTime? HireDate { get; set; }
            public string Address { get; set; }
            public string City { get; set; }
            public string Region { get; set; }
            public string PostalCode { get; set; }
            public string Country { get; set; }
            public string HomePhone { get; set; }
            public string Extension { get; set; }
            public string Notes { get; set; }
            public int? ReportsTo { get; set; }
    
            public virtual EmployeePhoto EmployeePhoto { get; set; }
            public virtual EmployeeEducation EmployeeEducation { get; set; }
        }
    
        public class EmployeePhoto
        {
            [Key]
            public int EmployeeID { get; set; }
            public byte[] Photo { get; set; }
            public string PhotoPath { get; set; }
        }
    
        public class EmployeeEducation
        {
            [Key]
            public int EmployeeID { get; set; }
            public string Schcool { get; set; }
            public string SchcoolTel { get; set; }
        }

    dbcontext.cs

    public DbSet<Employee> Employees { get; set; }
            public DbSet<EmployeePhoto> EmployeePhoto { get; set; }
    
            public DbSet<EmployeeEducation> EmployeeEducation { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {         
                modelBuilder.Entity<Employee>()
                    .HasRequired(e => e.EmployeePhoto)
                    .WithRequiredPrincipal();
    
                modelBuilder.Entity<Employee>()
                   .HasRequired(e => e.EmployeeEducation)
                   .WithRequiredPrincipal();
    
                modelBuilder.Entity<Employee>().ToTable("Employees");
                modelBuilder.Entity<EmployeePhoto>().ToTable("Employees");
                modelBuilder.Entity<EmployeeEducation>().ToTable("Employees");
    
            }

    Pro_Gaming.Infrastructure.IdentityUserLogin: : EntityType 'IdentityUserLogin' has no key defined. Define the key for this EntityType.

    According to your error message, it seems that you have not defined a key on the entity, please check and create key on it.

    In addition, Per your requirement, you could use entity framework TPH approach to achieve it. for more information about TPH, please refer to:

    http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 20, 2016 7:57 AM
  • User-1134857695 posted

    @Cole Wu: Man, you just saved my  life. It is working. I needed to do some modifications, which I'll outline below:

     protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                //Do not delete this:
                base.OnModelCreating(modelBuilder);
    
                modelBuilder.Entity<Tournament>()
                    .HasKey(e => e.TournamentId)
                    .HasRequired(e => e.Rules)
                    .WithRequiredPrincipal();
    
                modelBuilder.Entity<Tournament>()
                    .HasKey(e => e.TournamentId)
                    .HasRequired(e => e.TournamentSettings)
                    .WithRequiredDependent();
    
                modelBuilder.Entity<TournamentOrganizer>()
                    .HasKey(e => e.Id)
                    .HasRequired(e => e.Settings)
                    .WithRequiredDependent();
    
    
                modelBuilder.Entity<Tournament>().ToTable("Tournament");
                modelBuilder.Entity<TournamentRules>().ToTable("Tournament");
                modelBuilder.Entity<TournamentSettings>().ToTable("Tournament");
    
                modelBuilder.Entity<TournamentOrganizer>().ToTable("TournamentOrganizer");
                modelBuilder.Entity<TournamentOrganizerSetting>().ToTable("TournamentOrganizer");
    
               
    
            }

    So, let me explain (for any future references):

    • There is no need for partial classes (I say this because there is an example that states that you need partial classes, this is not true):
    • I haven't tested this, but I used the same key for all the classes that I wanted to share the same table.
    • modelBuilder.Entity<TheEntity> <= TheEntity will be the main class you want everything mapped to.
    • If you are using ASP.NET Identity and you are extending from IdentityDbContext<AppUser> (which is my case), It is very important to include base.OnModelCreating(modelBuilder) in the  OnModelCreating method,  otherwise you'll be hit with Identity issues that it doesn't find the primary key for IdenittyUser.
    • You would then use modelBuilder.Entity<Entity1>.ToTable("MyTable")
    • modelBuilder.Entity<Entity2>.ToTable("MyTable")
    • modelBuilder.Entity<Entity3>.ToTable("MyTable")
    • This will map Entity1, Entity2, Entity3, etc to MyTable.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 22, 2016 12:00 AM

All replies

  • User-1134857695 posted

    bump*

    Tuesday, April 19, 2016 10:34 PM
  • User1559292362 posted

    Hi superjose,

    First of all, is it possible? 

    Yes, It's possible, and I create a demo as below for your reference.

    model.cs

    public class Employee
        {
            public int EmployeeID { get; set; }
            public string LastName { get; set; }
            public string FirstName { get; set; }
            public string Title { get; set; }
            public string TitleOfCourtesy { get; set; }
            public DateTime? BirthDate { get; set; }
            public DateTime? HireDate { get; set; }
            public string Address { get; set; }
            public string City { get; set; }
            public string Region { get; set; }
            public string PostalCode { get; set; }
            public string Country { get; set; }
            public string HomePhone { get; set; }
            public string Extension { get; set; }
            public string Notes { get; set; }
            public int? ReportsTo { get; set; }
    
            public virtual EmployeePhoto EmployeePhoto { get; set; }
            public virtual EmployeeEducation EmployeeEducation { get; set; }
        }
    
        public class EmployeePhoto
        {
            [Key]
            public int EmployeeID { get; set; }
            public byte[] Photo { get; set; }
            public string PhotoPath { get; set; }
        }
    
        public class EmployeeEducation
        {
            [Key]
            public int EmployeeID { get; set; }
            public string Schcool { get; set; }
            public string SchcoolTel { get; set; }
        }

    dbcontext.cs

    public DbSet<Employee> Employees { get; set; }
            public DbSet<EmployeePhoto> EmployeePhoto { get; set; }
    
            public DbSet<EmployeeEducation> EmployeeEducation { get; set; }
    
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {         
                modelBuilder.Entity<Employee>()
                    .HasRequired(e => e.EmployeePhoto)
                    .WithRequiredPrincipal();
    
                modelBuilder.Entity<Employee>()
                   .HasRequired(e => e.EmployeeEducation)
                   .WithRequiredPrincipal();
    
                modelBuilder.Entity<Employee>().ToTable("Employees");
                modelBuilder.Entity<EmployeePhoto>().ToTable("Employees");
                modelBuilder.Entity<EmployeeEducation>().ToTable("Employees");
    
            }

    Pro_Gaming.Infrastructure.IdentityUserLogin: : EntityType 'IdentityUserLogin' has no key defined. Define the key for this EntityType.

    According to your error message, it seems that you have not defined a key on the entity, please check and create key on it.

    In addition, Per your requirement, you could use entity framework TPH approach to achieve it. for more information about TPH, please refer to:

    http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, April 20, 2016 7:57 AM
  • User-1134857695 posted

    Sweet! Thanks a bunch Cole! I'll be diving into right now!

    Wednesday, April 20, 2016 11:58 AM
  • User-1134857695 posted

    @Cole Wu: Man, you just saved my  life. It is working. I needed to do some modifications, which I'll outline below:

     protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                //Do not delete this:
                base.OnModelCreating(modelBuilder);
    
                modelBuilder.Entity<Tournament>()
                    .HasKey(e => e.TournamentId)
                    .HasRequired(e => e.Rules)
                    .WithRequiredPrincipal();
    
                modelBuilder.Entity<Tournament>()
                    .HasKey(e => e.TournamentId)
                    .HasRequired(e => e.TournamentSettings)
                    .WithRequiredDependent();
    
                modelBuilder.Entity<TournamentOrganizer>()
                    .HasKey(e => e.Id)
                    .HasRequired(e => e.Settings)
                    .WithRequiredDependent();
    
    
                modelBuilder.Entity<Tournament>().ToTable("Tournament");
                modelBuilder.Entity<TournamentRules>().ToTable("Tournament");
                modelBuilder.Entity<TournamentSettings>().ToTable("Tournament");
    
                modelBuilder.Entity<TournamentOrganizer>().ToTable("TournamentOrganizer");
                modelBuilder.Entity<TournamentOrganizerSetting>().ToTable("TournamentOrganizer");
    
               
    
            }

    So, let me explain (for any future references):

    • There is no need for partial classes (I say this because there is an example that states that you need partial classes, this is not true):
    • I haven't tested this, but I used the same key for all the classes that I wanted to share the same table.
    • modelBuilder.Entity<TheEntity> <= TheEntity will be the main class you want everything mapped to.
    • If you are using ASP.NET Identity and you are extending from IdentityDbContext<AppUser> (which is my case), It is very important to include base.OnModelCreating(modelBuilder) in the  OnModelCreating method,  otherwise you'll be hit with Identity issues that it doesn't find the primary key for IdenittyUser.
    • You would then use modelBuilder.Entity<Entity1>.ToTable("MyTable")
    • modelBuilder.Entity<Entity2>.ToTable("MyTable")
    • modelBuilder.Entity<Entity3>.ToTable("MyTable")
    • This will map Entity1, Entity2, Entity3, etc to MyTable.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 22, 2016 12:00 AM