CODEWITHSUNDEEP
entity-framework-4ef-code-first
Bob.at.Indigo.Health
Bob.at.Indigo.Health

Reputation: 11895

EF code-first optional 1-to-1 relationship

I have an EF data model that represents a report with a hierarchical tree of report sections. Each ReportSection entity contains a collection of zero or more child ReportSections. Each Report entity contains a single ReportSection entity that serves as the root of tree of ReportSections.

My data model that has the following navigation properties:

public class Report
{
    // Primary key
    public int Id { get; set; }

    // A Report has one root ReportSection
    [ForeignKey("RootReportSection")]
    public int ReportSectionId { get; set; }
    public virtual ReportSection RootReportSection { get; set; }
}

public class ReportSection
{
    // Primary key
    public int Id { get; set; }

    // Optional self-reference to the parent ReportSection
    [ForeignKey("ParentReportSection")]
    public int? ParentReportSectionId { get; set; }
    public virtual ReportSection ParentReportSection { get; set; }

    // Optional foreign key to the parent Report
    [ForeignKey("ParentReport")]
    public int? ReportId { get; set; }
    public virtual Report ParentReport { get; set; }

    // Child ReportSections contained in this ReportSection
    public virtual ICollection<ReportSection> ReportSections { get; set; }
}

Everything works fine if I omit the ReportSectionId and RootReportSection navigation claptrap from the Report entity. But, as coded above, attempting to add a migration gets an error:

Because the Dependent Role properties are not the key properties,
the upper bound of the multiplicity of the Dependent Role must be '*'.

After a bit of digging, I now understand that EF apparently wants me to use the primary key of my ReportSections entity as the foreign key to my Report entity. But, in my scenario, only the top-level ReportSection in a hierarchical tree of ReportSection entities participates in a relationship with a Report entity. The rest of the ReportSection entities are related to each other, and their primary keys are independent of any Report primary keys.

Is there a way to get this to work? Specifically, is there a way for a Report entity to "contain" a top-level ReportSection entity, which ReportSection entity has its own collection of self-referenced ReportSection entities?

Upvotes: 0

Views: 164

Answers (1)

Slauma
Slauma

Reputation: 177133

Apparently ReportSection is the principal in the relationship und Report the dependent (because Report must refer to an existing RootReportSection since Report.ReportSectionId is not nullable). In this case it is well possible that a ReportSection exists without a related Report. All your child ReportSection would have no Report.

But this can only work if the key in Report is not autogenerated because the key would have to be the same as the key of the related (and already existing) RootReportSection. So, you could try to model it like this:

public class Report
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    [ForeignKey("RootReportSection")]
    public int Id { get; set; }

    public virtual ReportSection RootReportSection { get; set; }
}

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

    // Optional self-reference to the parent ReportSection
    [ForeignKey("ParentReportSection")]
    public int? ParentReportSectionId { get; set; }
    public virtual ReportSection ParentReportSection { get; set; }

    // Optional foreign key to the parent Report
    public virtual Report ParentReport { get; set; }

    // Child ReportSections contained in this ReportSection
    public virtual ICollection<ReportSection> ReportSections { get; set; }
}

(It's possible that [DatabaseGenerated(DatabaseGeneratedOption.None)] is redundant because EF understands that the key cannot be database generated in this one-to-one relationship. I'm not 100% sure though.)

Downside of such a one-to-one relationship with shared primary key is that you could not change the relationship between Report and RootReportSection, i.e. you cannot have the Report refer to any other section than the one that has the same primary key.

If that doesn't work for you, you have to model the relationship as one-to-many because EF does not support one-to-one relationships with separate foreign key. Either remove that part altogether...

[ForeignKey("ParentReport")]
public int? ReportId { get; set; }
public virtual Report ParentReport { get; set; }

...if you don't need to navigate from the section to the report or replace it by a collection:

public virtual ICollection<Report> ParentReports { get; set; }

You would have to ensure in business logic that never more than one report is added to this collection to have kind of a simulation of a one-to-one relationship. In the database you could add a unique constraint on Report.ReportSectionId to have data integrity on database level. But EF won't understand this constraint and still allow to add more than one item to the collection. However, if you try to save it you'd get a unique key violation exception from the database.

Upvotes: 1

Related Questions