.Net Entity Framework Cyclic Cascade Paths

Question

I'm having some issues creating foreign key relationships between my tables using the C# Entity Framework. I have these tables:

CREATE TABLE [dbo].[Course] (
    [Id]                 INT           IDENTITY (1, 1) NOT NULL,,
    CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[PreqEdge] (
    [Id]          INT IDENTITY (1, 1) NOT NULL,
    [ParentID] INT NOT NULL,
    [ChildID]   INT NOT NULL,
    CONSTRAINT [PK_PreqEdge] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Dependant] FOREIGN KEY ([ParentID]) REFERENCES [dbo].[Course] ([Id]),
    CONSTRAINT [FK_Depends] FOREIGN KEY ([ChildID]) REFERENCES [dbo].[Course] ([Id])
);

The corresponding models look like so:

public partial class Course
{
    public int Id { get; set; }

    public virtual ICollection<PreqEdge> Parents { get; set; }
    public virtual ICollection<PreqEdge> Children { get; set; }

    public Course()
    {
        Parents = new HashSet<PreqEdge>();
        Children = new HashSet<PreqEdge>();
    }
}

public partial class PreqEdge
{
    public int Id { get; set; }
    public int ParentID { get; set; }
    public int ChildID { get; set; }

    public virtual Course Parent { get; set; }
    public virtual Course Child { get; set; }
}

Whenever I use my database context to access them I get this error:

Introducing FOREIGN KEY constraint 'FK_dbo.PreqEdges_dbo.Courses_ChildID' on table 'PreqEdges' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

The purpose of these tables is to connect two Courses together like the edge of a graph in order to create a chain of prerequisites for that course that can be traversed either forwards or backwards based on whether you filter by ChildID or ParentID.

I understand that Entity Framework by default uses cascade deletes on references and that I might be able to solve this by overriding the OnModelCreating method in my DbContext, but I'm not sure how to specify using a DbModelBuilder this kind of relationship where it could be foreign keyed with a table by either one of two keys in that table.

Is this possible to do in the Entity Framework without manually writing SQL calls?

EDIT: Changed DependantID and DependsID to ParentID and ChildID for clarity

Vojtěch Dohnal · Answer 1 · 2015-04-09T07:23:14.793

3

You will have to check for circular references with your model setup like that. In this model every course can connect with every course. It might be easier to reduce the flexibility of your model somehow, if possible.

Here is Object model as an example of various circular reference situations you can get:

Object model

Inspiration how to reduce the chance of circular reference:

Class Model

You can divide Courses into groups by CourseType and e.g. SpecializedCourse cannot be a Parent of any Course but can be a Child of a GeneralCourse.

edited Apr 09 '15 at 07:23

answered Apr 09 '15 at 07:08

Vojtěch Dohnal

7,867
3
43
105

Thank you for your insightful response! I think your schema is much better as it is more general and will prevent a circular case. We actually considered a circular case, but decided to ignore it for now since our prerequisite checks will always terminate before a certain depth in our tree (specified by the number of semesters you can take). In the case where it terminates on the max depth it will just error on that course it is checking. Thank you again for your input! – Zeke Gunnink Apr 17 '15 at 05:22

score 1 · Answer 2 · edited May 23 '17 at 12:29

I found a link with the correct solution. For this example you would do something like this:

public class Course
{
    public int Id { get; set; }
    public string CouresNumber { get; set; }

    public virtual ICollection<PreqEdge> Parents { get; set; }
    public virtual ICollection<PreqEdge> Children { get; set; }
}

public class PreqEdge
{
    public int Id { get; set; }
    public int ParentId { get; set; }
    public int ChildId { get; set; }

    public virtual Course Parent { get; set; }
    public virtual Course Child { get; set; }
}

public class CourseContext : DbContext
{
    public DbSet<Course> Courses { get; set; }
    public DbSet<PreqEdge> PreqEdges { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PreqEdge>()
                    .HasRequired(e => e.Parent)
                    .WithMany(c => c.Parents)
                    .HasForeignKey(e => e.ParentId)
                    .WillCascadeOnDelete(false);

        modelBuilder.Entity<PreqEdge>()
                    .HasRequired(e => e.Child)
                    .WithMany(c => c.Children)
                    .HasForeignKey(e => e.ChildId)
                    .WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }
}

Another mistake I made was to try to mix code first and database first.

Lali · Answer 3 · 2015-04-17T12:54:24.740

1

Try two ways.

1) set lazy loading off in constructor of DbContext class

   this.Configuration.LazyLoadingEnabled = false;

It will not allow to load referencing objects

or

2) use [notmapped] on PreqEdge referencing properties as 

[notmapped]    
public virtual Course Parent { get; set; }
[notmapped]
public virtual Course Child { get; set; }

This is just to break the cycle.

edited Apr 17 '15 at 12:54

answered Apr 09 '15 at 07:33

Lali

2,816
4
30
47

.Net Entity Framework Cyclic Cascade Paths

3 Answers3