SQL Server CE identifies a cyclical reference with Entity Framework Code First but SQL Server 2008 does not

Question

I am working on an Entity Framework Code First project that has a fairly complex Data Model which deploys absolutely fine on SQL Server 2008.

However when creating an SQL Server CE database for some local End-To-End testing I get the following error message when EF creates the database:

System.Data.SqlServerCe.SqlCeException: The referential relationship will result in a cyclical reference that is not allowed. [ Constraint name = FK_Sites_Persons_PersonId ].

I have disabled the ManyToManyCascadeDeleteConvention in my DataContext model creation method, so that isn't the cause of the problem. The trouble I have is that the relationship in question looks fine in the SQL Server 2008 database- it appears to be a normal foreign key from what I can tell and I can't see anything flowing back in the other direction, although it is not impossible that there is a longer-path circular reference. I don't know why CE would fail and 2008 would succeed.

Define your FK's as nullable. By convention, the Entity Framework enables cascade delete for non-nullable foreign keys. — VahidN, Oct 08 '12 at 20:37

score 3 · Accepted Answer · answered Oct 10 '12 at 11:08

3

It turns out the problem was very simply solved- although I had disabled ManyToManyCascadeDeleteConvention I also needed to disable the OneToManyCascadeDeleteConvention to avoid the circular reference problem.

answered Oct 10 '12 at 11:08

glenatron

11,018
13
64
112

score 2 · Answer 2 · answered Oct 14 '12 at 17:58

You might also consider explicitly defining the cascading updates and deletes rather than disabling them globally. Assume a model:

namespace Models
{
    public class Parent
    {
        public Parent() { this.Children = new HashSet<Child>(); }

        public int id { get; set; }
        public string description { get; set; }

        public ICollection<Child> Children { get; set; }
    }

    public class Child
    {
        public int id { get; set; }
        public string description { get; set; }

        public Parent Parent { get; set; }
    }
}

Override the OnModelCreating in your context and use the fluent api to specify the cascade options for a given relationship.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Parent>().HasMany<Child>(p => p.Children).WithRequired(c => c.Parent).WillCascadeOnDelete(false);

        base.OnModelCreating(modelBuilder);
    }

Of course this is a simple example, but you can apply the same principle to your down-level entities and specifically exclude the cascaded delete that causes the circular reference.

Thanks, this is a good answer- doesn't quite work out for me because of the slightly quirky EF configuration I am using, but for people finding this in future I suspect this will be a more complete solution. — glenatron, Oct 15 '12 at 10:54

SQL Server CE identifies a cyclical reference with Entity Framework Code First but SQL Server 2008 does not

2 Answers2