CODEWITHSUNDEEP
c#entity-frameworksqliteentity-framework-6
Jay Wick
Jay Wick

Reputation: 13737

Two tables referencing each other in Entity Framework 6 Code First

I have the entities Project and Sprint where a Sprint belongs to a Project. A Project also contains a Backlog which is a reference to a single Sprint it will add items to by default.

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; }
}

Entity Framework obviously can't determine the relationship between these two entities just from the above and throw

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 been trial-and-erroring a lot and cannot get passed various issues such as 'Multiplicity is not valid in Role' issues.

How do correctly I model this relationship I've described correctly using either Data Annotations or OnModelCreating(). I currently have

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

Background: I'm using EF6 and connecting to an Sqlite file using the System.Data.SQLite.EF6 provider

Upvotes: 2

Views: 2855

Answers (3)

Frank Rem
Frank Rem

Reputation: 3708

This works with EF Core.

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

    // explicit constraint is required to resolve child-dependent relationship
    [ForeignKey("BacklogId")]
    public virtual Sprint Backlog { get; set; }
}

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

    // explicit constraint is required to resolve child-dependent relationship
    [ForeignKey("ProjectId")]
    public virtual Project Project { get; set; }
}

var project = new Project { Name = "Some project" };

// intermediate saving is required to avoid circular dependency exception
context.Projects.Add(project);
context.SaveChanges();

var backlog = new Sprint { Name = "Backlog sprint", Project = project };
project.Backlog = backlog;

context.Sprints.Add(backlog);
context.SaveChanges();

Upvotes: 0

Jay Wick
Jay Wick

Reputation: 13737

The following OnModelCreating() worked and seems correct.

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);
}

It was important to set Backlog as optional using HasOptional using modelBuilder and making Project.BacklogId nullable with long?. otherwise there's a circular reference and we won't be able to create either entity. It made sense to keep the Backlog reference on Project (as it belongs to the Project) instead of creating an IsBacklog on Sprint.

With the additional reference to Project's collection of Sprint - thanks to @fejesjoco and @Zakos for pointing this out

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 Note

Even though Project.BacklogId is nullable, Entity Framework sees this as a circular reference and throws a DbUpdateException:

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

As it turns out there's ticket #142 on EF's bug/idea list reported in late 2012 with exact same problem. It's current status is proposed and there's a comment by an EF rep saying:

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

The way to overcome this is to save to the context twice inside a 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;
    }
}

Upvotes: 3

fejesjoco
fejesjoco

Reputation: 11903

I think you actually need two distinct relationships. There's a 1:0..N between project and sprint (all the sprints of a project), and 0..1:1 between project and sprint (the backlog sprint of the project), so you need two model builder statements. Sprint.ProjectId is the FK of the first association, Project.BacklogId is of the second. Of course, this layout allows you to specify a backlog which doesn't belong to the project, so you need to validate that. Alternatively, you could introduce a Sprint.IsBacklog flag, in that case you would only need one association.

Upvotes: 2

Related Questions