locked
Fluent API One to Many on existing database RRS feed

  • Question

  • User19694046 posted

    Hello,

    I am working with an existing database that I cannot alter.  I want to represent the one to many relationship between EUPerson and EUPersonName using fluent API, but just cannot seem to get it correct.   

    1.  The primary key on the table Names_Hx which EUPersonName represents is record_id. 

    2.  The primary key on the EC_emp table which EUPerson represents is user_id.

    3.  If I were joining the actual tables, I would be joining them on EC_emp.name = Names_Hx.record_name. 

    4.  EC_emp.name is represented in EUPerson by the ec_name inherited from the ISUser class. 

    This is one of many variations I have tried:

    modelBuilder.Entity<EUPerson>()

                    .HasMany(u => u.euPersonNames)

                    .WithRequired()

                    .Map(t => t.MapKey("ec_name"));

                                             

               modelBuilder.Entity<EUPersonName>()

                    .HasRequired(u => u.EUPerson)

                    .WithMany(h => h.euPersonNames)

                    .Map(h => h.MapKey("name"));

      

    Please help!

    Thank you very much!!

    Here is the code for all the classes: 

       public abstract partial class ISUserEC

        {

            [Key]

            public string ec_user_id { get; set; }

            public string ec_name { get; set; }

            public DateTime? ec_eff_from_date { get; set; }

        }  

      public partial class EUPerson : ISUserEC

        {

            public EUPerson()

            {

                this.euPersonNames = new HashSet<EUPersonName>();

            }

            public string e_login { get; set; }

            public int e_user_status_c { get; set; }

            public virtual ICollection<EUPersonName> euPersonNames { get; set; }

                 }

       public class EUPersonName

        {

            [Key]

            public int record_id { get; set; }

            public string record_name { get; set; }                        

            public string first_name { get; set; }

            public string last_name { get; set; }

            public string middle_name { get; set; }

            public DateTime INSTANT_OF_UPD_TM { get; set; }

            public virtual EUPerson EUPerson { get; set; }

        }

     public class ECContext : DbContext

        {

            public ECContext()

                : base("ECContext")

            {

            }

            public virtual DbSet<ISUserEC> ISUsersEC { get; set; }

            public virtual DbSet<EUPerson> EUPersons { get; set; }

            public virtual DbSet<EUPersonName> euPersonNames { get; set; }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)

            {

                modelBuilder.Entity<ISUserEC>().ToTable("EC_Emp");

                modelBuilder.Entity<ISUserEC>().Property(t => t.ec_name).HasColumnName("name");

                //mapping the TPH inheritance

                //EUPerson is a type of ISUserEC

                modelBuilder.Entity<ISUserEC>()

                    .Map<EUPerson>(m => m.Requires("emp_record_type_c").HasValue("1"));

              

    modelBuilder.Entity<EUPersonName>().ToTable("Names_hx");        

                modelBuilder.Entity<EUPerson>()

                    .HasMany(u => u.euPersonNames)

                    .WithRequired()

                    .Map(t => t.MapKey("ec_name"));

                                             

               modelBuilder.Entity<EUPersonName>()

                    .HasRequired(u => u.EUPerson)

                    .WithMany(h => h.euPersonNames)

                    .Map(h => h.MapKey("name"));

            }

        }

    Friday, May 5, 2017 9:31 PM

Answers

  • User-707554951 posted

    Hi Ygwywf.

    For your error message. You need to Add the following to your web.config:

     modelBuilder.Entity<EUPersonName>()
                        .HasRequired<EUPerson>(s => s.EUPerson) // EUPersonName entity requires EUPersonName 
                        .WithMany(s => s.EUPersonName); 

    For how to configure one to many relationship in ef, please refer to the folwoing links:

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx

    http://www.c-sharpcorner.com/UploadFile/3d39b4/relationship-in-entity-framework-using-code-first-approach-w/

    http://stackoverflow.com/questions/30201495/how-to-configure-a-one-to-many-relationship-in-ef

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 8, 2017 9:08 AM
  • User19694046 posted

    Hi Cathy,

    Thank you for your response.  This is only a partial answer, as I had issues with the keys.  My current solution include:

    1.  setting the [Key] attribute in the ISUser class on the ec_name property instead of ec_user_id.  The actual primary key in the db is on user_id which ec_user_id represents.  I would like to be able to indicate this via fluent api instead of the key attribute. 

    2. Only using one section in the context (not the web.config) and adding the .HasForeignKey(u => u.record_name)

    so:

    // modelBuilder.Entity<EpicUserPerson>() 

    //    .HasMany(u => u.epicUserPersonNames)

    //    .WithRequired()

    //    .Map(t => t.MapKey("ec_name"));

     modelBuilder.Entity<EUPersonName>().ToTable("Names_static");

      modelBuilder.Entity<EUPersonName>()

                    .HasRequired<EUPerson>(u => u.eUserPerson)

                    .WithMany(h => h.eUPersonNames)

                    .HasForeignKey(u => u.record_name);  //changed this property to match the db column name

    Thank you!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 8, 2017 4:13 PM

All replies

  • User-707554951 posted

    Hi Ygwywf.

    For your error message. You need to Add the following to your web.config:

     modelBuilder.Entity<EUPersonName>()
                        .HasRequired<EUPerson>(s => s.EUPerson) // EUPersonName entity requires EUPersonName 
                        .WithMany(s => s.EUPersonName); 

    For how to configure one to many relationship in ef, please refer to the folwoing links:

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx

    http://www.c-sharpcorner.com/UploadFile/3d39b4/relationship-in-entity-framework-using-code-first-approach-w/

    http://stackoverflow.com/questions/30201495/how-to-configure-a-one-to-many-relationship-in-ef

    Best regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 8, 2017 9:08 AM
  • User19694046 posted

    Hi Cathy,

    Thank you for your response.  This is only a partial answer, as I had issues with the keys.  My current solution include:

    1.  setting the [Key] attribute in the ISUser class on the ec_name property instead of ec_user_id.  The actual primary key in the db is on user_id which ec_user_id represents.  I would like to be able to indicate this via fluent api instead of the key attribute. 

    2. Only using one section in the context (not the web.config) and adding the .HasForeignKey(u => u.record_name)

    so:

    // modelBuilder.Entity<EpicUserPerson>() 

    //    .HasMany(u => u.epicUserPersonNames)

    //    .WithRequired()

    //    .Map(t => t.MapKey("ec_name"));

     modelBuilder.Entity<EUPersonName>().ToTable("Names_static");

      modelBuilder.Entity<EUPersonName>()

                    .HasRequired<EUPerson>(u => u.eUserPerson)

                    .WithMany(h => h.eUPersonNames)

                    .HasForeignKey(u => u.record_name);  //changed this property to match the db column name

    Thank you!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 8, 2017 4:13 PM
  • User19694046 posted

    Using Fluent api to set the key:

    modelBuilder.Entity<ISUserEC>().Property(t => t.ec_name).HasColumnName("name");

    modelBuilder.Entity<ISUserEC>().HasKey(t => t.ec_name);

    Tuesday, May 9, 2017 4:07 PM