CODEWITHSUNDEEP
c#.netsql-serverasp.net-mvc
Zeke Gunnink
Zeke Gunnink

Reputation: 65

.Net Entity Framework Cyclic Cascade Paths

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

Upvotes: 1

Views: 875

Answers (3)

Lali
Lali

Reputation: 2866

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.

Upvotes: 1

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8102

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.

Upvotes: 3

Zeke Gunnink
Zeke Gunnink

Reputation: 65

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.

Upvotes: 1

Related Questions