locked
Entity Framework 4.1 Mapping One to Many Fluent API RRS feed

  • Question

  • I am having a difficult time trying to get the One to Many mapping accomplished on the following Objects.

        public class County_Codes
        {
            public String County_cd { get; set; }
    		public String County_st { get; set; }
            public String County_name { get; set; }
    
            public virtual CS_Property_Details PropertyDetails { get; set; }
        }
    
    	public class CS_Property_Details
    	{
    		public Decimal Loan_id { get; set; }
    		public Int16 Property_id { get; set; }
    		public String Property_county_cd { get; set; }
    		
    		public virtual ICollection<County_Codes> CountyCodes { get; set; }
    	}
    	
    	public CountyCodesMapping()
        {
    		ToTable("county_Codes");
    		HasKey(o => o.County_cd);
    		Property(o => o.County_cd).IsRequired().HasColumnName("county_cd");
    		Property(o => o.County_name).HasColumnName("county_name");
    		Property(o => o.County_st).HasColumnName("county_st");
    	}
    	
    	public CS_Property_DetailsMapping()
    	{
    		ToTable("cs_property_details");
    		HasKey(o => new { o.Loan_id, o.Property_id });
    
    		Property(o => o.Loan_id).IsRequired().HasColumnName("Loan_id");
    		Property(o => o.Property_id).IsRequired().HasColumnName("property_id");
    		Property(o => o.Property_county_cd).HasColumnName("Property_county_cd");
    		
    		HasMany(o => o.CountyCodes).WithRequired(o=>o.PropertyDetails).HasForeignKey(o => o.County_cd);
    	}
    

    I get this error:

    Multiplicity is not valid in Role 'CS_Property_Details_CountyCodes_Target' in relationship 'CS_Property_Details_CountyCodes'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be �1�.
     System.Data.Edm.EdmAssociationConstraint: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.

    If I try this mapping:

    HasMany(o => o.CountyCodes).WithRequired(o => o.PropertyDetails).Map(m => m.MapKey("Property_county_cd"));

    I get this error:

    The specified association foreign key columns 'Property_county_cd' are invalid. The number of columns specified must match the number of primary key columns.

    Anyone have any suggestions?


    Bill Behning

    • Moved by Caillen Thursday, February 13, 2014 1:17 AM
    Thursday, February 13, 2014 12:30 AM

Answers

  • I should have mentioned that the back end here is a third party database and I can't change the schema, which means I can't add fields that do not map to the schema.

    Actually, now that I looked at it further, this needs to be a one to one realation.  Initially I ran this query to determine the relation:

    SELECT COUNT(*),loan_id,property_county_cd
    FROM dbo.cs_property_details
    GROUP BY loan_id,property_county_cd
    HAVING COUNT(*) >1

    This returned many rows.  What I should have run was this:

    SELECT COUNT(*),loan_id,property_id ,property_county_cd
    FROM dbo.cs_property_details
    GROUP BY loan_id,property_id,property_county_cd
    HAVING COUNT(*) >1

    This returned 0 rows, which means it should be a one to one relation.

    I will attempt to configure this as a one to one relation and let you know the outcome.

    Yep, that was it.  Trying to fit a square peg into a round hole.

    Changed the Property_Details class:

    public virtual County_Codes CountyCodes { get; set; }
    
     

    Removed from the County_Codes class:

    public virtual CS_Property_Details PropertyDetails { get; set; }

    Then in the Property_Details Mapping:

    HasRequired(o => o.CountyCodes).WithMany().HasForeignKey(o => o.Property_county_cd);

    Works like a champ.

    Thanks for you help on this.

    Bill Behning



    • Edited by WRBehning Friday, February 14, 2014 5:23 PM
    • Marked as answer by Fred Bao Friday, February 21, 2014 10:15 AM
    Friday, February 14, 2014 1:59 PM

All replies

  • Hello,

    A key can be a foreign key at the same time, but not in a one-to-many relationship. Have a try to add a foreign key field in County_Codes class like:

    public String Property_county_cd { get; set; }

    Then specify this field to be foreign key:

    HasMany(o => o.CountyCodes).WithRequired(o=>o.PropertyDetails).HasForeignKey(o => o. Property_county_cd);

    If this does not work for you, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, February 13, 2014 1:29 PM
  • Gave that a try, now I get:

    The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.

    I removed the navigation fields from both classes to make sure adding the new field did not mess things up.

    Since there is no field in the Count_Codes table to map it to,  I tried mapping it to county_cd, but that threw this exception:

    Schema specified is not valid. Errors:
    (155,6) : error 0019: Each property name in a type must be unique. Property name 'county_cd' was already defined.

    I redesigned the County_Codes class as such:

        public class County_Codes
        {
            public String Property_county_cd { get; set; }
    		public String County_st { get; set; }
            public String County_name { get; set; }
    
            public virtual CS_Property_Details PropertyDetails { get; set; }
        }
    

    Mapping:

    public CountyCodesMapping()
        {
    		ToTable("county_Codes");
    		HasKey(o => o.Property_county_cd);
    		Property(o => o.Property_county_cd).IsRequired().HasColumnName("county_cd");
    		Property(o => o.County_name).HasColumnName("county_name");
    		Property(o => o.County_st).HasColumnName("county_st");
    	}

    Now I get this exception:

    One or more validation errors were detected during model generation:

     System.Data.Edm.EdmAssociationEnd: : Multiplicity is not valid in Role 'CS_Property_Details_CountyCodes_Target' in relationship 'CS_Property_Details_CountyCodes'. Because the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be �1�.
     System.Data.Edm.EdmAssociationConstraint: : The number of properties in the Dependent and Principal Roles in a relationship constraint must be identical.


    Bill Behning

    Thursday, February 13, 2014 3:12 PM
  • Hello,

    Sorry for that, it should be my mistake, I do not notice that you have two fields as primary key. For this, add two additional correspondent fields in County_Codes class:

    public Decimal Loan_id { get; set; }

    public Int16 Property_id { get; set; }

    The mapped codes:

    HasMany(o => o.CountyCodes).WithRequired(o => o.PropertyDetails).HasForeignKey(o => new { o.Loan_id, o.Property_id });

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, February 14, 2014 7:11 AM
  • I should have mentioned that the back end here is a third party database and I can't change the schema, which means I can't add fields that do not map to the schema.

    Actually, now that I looked at it further, this needs to be a one to one realation.  Initially I ran this query to determine the relation:

    SELECT COUNT(*),loan_id,property_county_cd
    FROM dbo.cs_property_details
    GROUP BY loan_id,property_county_cd
    HAVING COUNT(*) >1

    This returned many rows.  What I should have run was this:

    SELECT COUNT(*),loan_id,property_id ,property_county_cd
    FROM dbo.cs_property_details
    GROUP BY loan_id,property_id,property_county_cd
    HAVING COUNT(*) >1

    This returned 0 rows, which means it should be a one to one relation.

    I will attempt to configure this as a one to one relation and let you know the outcome.

    Yep, that was it.  Trying to fit a square peg into a round hole.

    Changed the Property_Details class:

    public virtual County_Codes CountyCodes { get; set; }
    
     

    Removed from the County_Codes class:

    public virtual CS_Property_Details PropertyDetails { get; set; }

    Then in the Property_Details Mapping:

    HasRequired(o => o.CountyCodes).WithMany().HasForeignKey(o => o.Property_county_cd);

    Works like a champ.

    Thanks for you help on this.

    Bill Behning



    • Edited by WRBehning Friday, February 14, 2014 5:23 PM
    • Marked as answer by Fred Bao Friday, February 21, 2014 10:15 AM
    Friday, February 14, 2014 1:59 PM