Entity Framework, Code First modeling and a cyclical reference

Question

I've spend several days now, trying to solve this problem. While making a simple project to exemplify my problem, I stumbled upon a possible solution. So, this is sort of a double question.

But first, a little background info:

I just started using Entity Framework 4.1 (EF) and Code First to create the models for my ASP.NET MVC project. I need some models similar to this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TestApp.Models
{
    public class Family
    {
        public int ID { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Father> Fathers { get; set; }
        public virtual ICollection<Mother> Mothers { get; set; }
    }

    public class Mother
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int FamilyID { get; set; }

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

    public class Father
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int FamilyID { get; set; }

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

    public class Child
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int MotherID { get; set; }
        public int FatherID { get; set; }

        public virtual Mother Mother { get; set; }
        public virtual Father Father { get; set; }
    }
}

And the DbContext:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace TestApp.Models
{
    public class TestContext : DbContext
    {
        public DbSet<Family> Families { get; set; }
        public DbSet<Mother> Mothers { get; set; }
        public DbSet<Father> Fathers { get; set; }
        public DbSet<Child> Children { get; set; }
    }
}

(Please excuse the lame example, that's what my Friday fried brain was able to come up with.)

A family can have several mothers and several fathers. And a child has a mother and a father. I checked with one of the .NET gurus at my work, who agreed that there is nothing extraordinary in this. At least as far as we can see.

But when I run the code, I get this Exception:

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

I do see the cycle: Family - Mother - Child - Father - Family. But if I created the database tables myself (which I prefer not to, that's what I like about Code First) it would be a perfectly valid data structure, as far as I can tell.

So, my first question is: Why is this a problem when using code first? Is there a way to tell EF how to properly handle the cycle?

Then, as I write initially, while creating a simple project to exemplify my problem, I incidentally stumbled upon a possible solution. I simply forgot some of the properties when defining my models. For clarity in the following example, instead of removing them, I've commented out the parts of the models I forgot:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TestApp.Models
{
    public class Family
    {
        public int ID { get; set; }
        public string Name { get; set; }

        public virtual ICollection<Father> Fathers { get; set; }
        public virtual ICollection<Mother> Mothers { get; set; }
    }

    public class Mother
    {
        public int ID { get; set; }
        public string Name { get; set; }
        // public int FamilyID { get; set; }

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

    public class Father
    {
        public int ID { get; set; }
        public string Name { get; set; }
        // public int FamilyID { get; set; }

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

    public class Child
    {
        public int ID { get; set; }
        public string Name { get; set; }
        // public int MotherID { get; set; }
        // public int FatherID { get; set; }

        public virtual Mother Mother { get; set; }
        public virtual Father Father { get; set; }
    }
}

So, removing these SomethingID reference properties seems to solve my problem. As you can see in the controller of the sample project I'm linking to in the end of this post, I'm still able to cycle all the way around and do stuff like mothers.First().Family.Fathers.First().Children.First().Mother.Family.Name without any problems. But all tutorials and examples about EF and Code First modeling I've been looking at (e.g. this one by Scott Guthrie) include these properties, so it feels wrong not to use them.

And so, my second question is: Will there be any drawbacks and problems I haven't discovered yet doing this?

Download example project here: http://blackfin.cannedtuna.org/cyclical-reference-test-app.zip, and open TestSolution.sln. The properties are commented out in the example project. Uncomment the lines in TestModels.cs to add the properties, resulting in the cyclical reference exception.

NB: The solution is creating and seeding a SQL CE database located at c:\TestApp.sdf

Update, December 2011: I never solved this problem technically, but I quit my job and found another job where I don't have to use Microsoft technologies. That sort of solved my problem :)

As the tech support at the old place used to write when fixing issues: "A workaround or solution has been provided".

Answer 1

But if I created the database tables myself (which I prefer not to, that's what I like about Code First) it would be a perfectly valid data structure, as far as I can tell.

This is something you should double check. The exception comes directly from the database and not from Entity Framework. It's likely that also a table structure with the same constraints created by hand will be invalid. Keep in mind that your foreign key properties Mother.FamilyID, Father.FamilyID, Child.MotherID and Child.FatherID are not nullable, so they represent required relationships and the corresponding columns in the database are also not nullable.

When you remove all these properties from your model classes your relationships become suddenly optional because the navigation properties can be null. This is another model now since the FK columns in the DB can be nullable! Apparently this is an allowed model.

If you want to have still foreign key properties in your model which represent optional instead of required relationship you can use nullable types: public int? FamilyID { get; set; }, public int? MotherID { get; set; }, etc.