Two tables referencing each other in Entity Framework 6 Code First

c# entity-framework entity-framework-6 sqlite

Question

I have the things.Project and Sprint where a Project is the home of a Sprint. A Project includes an as well.Backlog This refers to the single Sprint that it will automatically add items to.

public class Project
{
    public long ID { get; set; }
    public string Name { get; set; }

    public long BacklogId { get; set; }
    public Sprint Backlog { get; set; }
}

public class Sprint
{
    public long ID { get; set; }
    public string Name { get; set; }

    public long ProjectId { get; set; }
    public Project Project { get; set; }
}

It is clear from the above that Entity Framework cannot infer the relationship between these two entities.

Additional information: Unable to determine the principal end of an association between the types 'Sprint' and 'Project'. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

I've tried many different approaches and have been having trouble getting past problems like "Multiplicity is not valid in Role."

How can I model this relationship correctly? I've accurately described utilizing Data Annotations orOnModelCreating() . Right now, I have

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Sprint>()
        .HasRequired(x => x.Project)
        .WithRequiredPrincipal(x => x.Backlog);
}

Background: I'm connecting to a Sqlite file with EF6 and theSystem.Data.SQLite.EF6 provider

1
2
11/23/2014 10:42:52 AM

Accepted Answer

The subsequentOnModelCreating() worked and seems to be accurate.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Sprint>()
        .HasKey(s => s.ID)
        .HasRequired(s => s.Project)
        .WithMany(p => p.Sprints)
        .HasForeignKey(s => s.ProjectId);

    modelBuilder.Entity<Project>()
        .HasKey(p => p.ID)
        .HasOptional(p => p.Backlog);
}

Setting Backlog as an optional setting usingHasOptional using modelBuilder and producingProject.BacklogId with nullabilitylong? in order to avoid a circular reference that prevents us from creating either entity. Keeping the Backlog reference on Project (as it is a part of the Project) made more sense thanIsBacklog using Sprint.

Along with the extra mention ofProject 's assortment ofSprint I appreciate @fejesjoco and @Zakos for bringing this to my attention.

public class Project
{
    public long ID { get; set; }
    public string Name { get; set; }

    public ICollection<Sprint> Sprints { get; set; } // new
    public long? BacklogId { get; set; } // changed
    public Sprint Backlog { get; set; }
}

public class Sprint
{
    public long ID { get; set; }
    public string Name { get; set; }

    public long ProjectId { get; set; }
    public Project Project { get; set; }
}

Important Reminder

in spite ofProject.BacklogId Entity Framework throws a DbUpdateException when it detects a circular reference that is nullable.

Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.

It turns out that the exact identical issue was reported as bug/idea citation #142 on EF's list in late 2012. Its planned status is stated, and an EF representative comments as follows:

We agree that this would be a good scenario to enable. Taking into account where we are in the EF6 release along with the size and the impact of this feature our team is not planning to implement it in EF6. Therefore, we are moving it to the Future release to reconsider in the next release.

- RoMiller wrote Jan 25, 2013 at 9:17 AM

Workaround

To get around this, you should do two context saves within a single transaction.

using (var transaction = context.Database.BeginTransaction())
{
    try
    {
        var project = new Project { Name = "Project 1" };
        context.Projects.Add(project);
        context.SaveChanges();

        var backlog = new Sprint { Name = "Backlog", Project = project };
        project.Backlog = backlog;
        context.Sprints.Add(backlog);
        context.SaveChanges();

        transaction.Commit();
    }
    catch (Exception)
    {
        transaction.Rollback();
        throw;
    }
}
2
5/23/2017 12:24:05 PM

Popular Answer

In my opinion, you actually require two separate relationships. You need two model builder statements because the relationship between a project and a sprint is 1:0..N (all the sprints of a project) and 0..1:1 (the backlog sprint of a project).Sprint.ProjectId is the first association's FK,Project.BacklogId is second-generation. Obviously, this style enables you to provide a backlog that is not associated with the project, therefore you must confirm that. As an alternative, you might add aSprint.IsBacklog if so, you would only require one association.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow