Multiplicity constraint violated Error while loading entity

Question

I've seen this question been asked number of times but each has its own scenario and i couldn't find solution.

I wanted to add a table that holds a survey data, and each SURVEY belongs to a SITE.

I tried adding a child table Site_Survey such that if forms One-to-Zero_or_One relationship to an exiting table Site_Report.

I'm using EF-6, Code first, with FluentApi for mapping. Following is how I've defined the Entities

Site_Survey

public class Site_Survey
    {
        // Scalar Properties
        public int site_survey_id { get; set; }
        // ..other properties removed for brevity


        // Navigation Properties
        public virtual Site_Report Site_Report { get; set; }
    }

Site_Report

public class Site_Report
    {
        public Site_Report()
        {
            this.Report_Assets = new HashSet<Report_Asset>();
            this.Site_Survey = new Site_Survey();
        }

        // Scalar Properties
        public int site_report_id { get; set; }
        // ..other properties removed for brevity

        // Navigation Properties
        // Site_Report one-to-zero_or_one Site_Survey
        public virtual Site_Survey Site_Survey { get; set; }
    }

Mapping

public class Site_SurveyMap : EntityTypeConfiguration<Site_Survey>
    {
        public Site_SurveyMap()
        {
            HasKey(x => x.site_survey_id);
        }
    }

public class Site_ReportMap : EntityTypeConfiguration<Site_Report>
    {
        public Site_ReportMap()
        {
            HasKey(one => one.site_report_id);

            // mapping for Site_Report one-to-zero_or_one Site_Survey
            HasOptional(o => o.Site_Survey)         // Mark Site_Survey property optional in Site_Report entity
            .WithRequired(o => o.Site_Report)       // Mark Site_Report property as required in Site_Survey entity. 
            .WillCascadeOnDelete(true);             // i.e. Cannot save Site_Survey without Site_Report
        }
    }

Im initialising Site_Survey inside Site_Report constructor so that every new Site_Report would have at most one survey. For old existing sites having no records in Survey table i'm adding code logic and populate them when necessary.

Problem

From DbContext, whilst loading the Site_Survey object on its own, I successfully get correct survey record

Site_Survey model = ctx.Site_Survey.Where(i => i.site_survey_id == id).FirstOrDefault();

But if I try to include Site_Report Entity in the context, it throws the error.

Site_Survey model = ctx.Site_Survey.Include(i => i.Site_Report).Where(i => i.site_survey_id == id).FirstOrDefault();

And vice versa if try loading Site_Report and include Site_Survey.

Error

"Multiplicity constraint violated. The role 'Site_Report_Site_Survey_Target' of the relationship 'StevensCalcertPortal.DBAccessLayer.Site_Report_Site_Survey' has multiplicity 1 or 0..1."

The fact that it actually is 1-0..1 relationship, what am i doing wrong ?

Answer 1

With help reading this article Entity framework and Table splitting with Code First. Following changes actually addressed my issue.

Keeping the Site_Report constructor as it was and using a separate static method CreateEntity() for generating objects. Also changing constructor's visibility to protected to force the use of static method where new Site_Report(); was needed in the code.

public class Site_Report
    {
        public static Site_Report CreateEntity()
        {
            return new Site_Report()
            {
                Site_Survey = new Site_Survey()
            };
        }

        protected Site_Report()
        {
            this.Report_Assets = new HashSet<Report_Asset>();
            // this.Site_Survey = new Site_Survey();
        }
    }

Basically error happens if you initialise Site_Survey in constructor this.Site_Survey = new Site_Survey();.

Inside EF after issuing query to the database, when construct an instance of Site_Report, it got the Site_Survey already populated with an ID of 0 and EF complains because it got different ID from the database, so what EF thinking that Site_Reportrefers to two distinct Site_Survey when the relation is 1:1 (they should share the very same id).