Entity Framework, Code First Simulation and Looping

Question

I spent several days trying to solve this problem. Carrying out a simple project to demonstrate my problem, I came across a possible solution. So this is kind of a double question.

But first, some background information:

I just started using Entity Framework 4.1 (EF) and Code First to create models for my ASP.NET MVC project. I need 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 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 my fried brain on Friday was able to come up with.)

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

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

System.Data.SqlServerCe.SqlCeException: The reference relation will result in a circular reference that is not resolved. [Constraint name = Mother_Family]

I see a cycle: Family - Mother - Child - Father - Family . But if I created the database tables myself (which I prefer not to do, what I like about Code First), as far as I can tell, this would be an absolutely reliable data structure.

So, my first question is: Why is this a problem when using code for the first time? Is there any way to tell EF how to handle the loop correctly?

Then, when I write initially, creating a simple project to demonstrate my problem, I accidentally stumbled upon a possible solution. I just forgot some properties when defining my models. For clarity, in the following example, instead of deleting them, I commented on the parts of the models that 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 reference properties from SomethingID seems to solve my problem. As you can see in the controller of the sample project that I attach to at the end of this post, I’m still able to spin mothers.First().Family.Fathers.First().Children.First().Mother.Family.Name and do something like mothers.First().Family.Fathers.First().Children.First().Mother.Family.Name no problem. But all the training materials and examples regarding EF and Code First modeling that I looked at (for example, this Scott Guthrie ) include these properties, so it feels like not to use them.

So, my second question: Will there be any flaws and problems that I have not yet discovered, yet did it?

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

NB: The solution creates and unloads the SQL CE database located in c: \ TestApp.sdf

Update, December 2011: I never solved this problem technically, but I quit my job and found another job where I do not need to use Microsoft technology. This kind of problem is resolved :)

As technical support in the old place, used to record when troubleshooting: "A workaround or solution was provided."

Answer 1

But if I created the database tables myself (which I prefer not to do, which I like in Code First), that would be perfectly fair as far as I can tell.

This must be double checked. The exception comes directly from the database, not from the Entity Framework. It is also likely that the table structure with the same restrictions that are created manually will also be invalid. Keep in mind that your foreign key properties Mother.FamilyID , Father.FamilyID , Child.MotherID and Child.FatherID are not null, so they represent the required relationships and the corresponding columns in the database also cannot be nullified.

When you remove all of these properties from model classes, your relationships become optional since the navigation properties may be null . This is another model, as the FK columns in the database can be nullified! This is apparently a legal model.

If you want to still have foreign key properties in your model that are optional and not necessary relationships, you can use types with a null value: public int? FamilyID { get; set; } public int? FamilyID { get; set; } public int? FamilyID { get; set; } , public int? MotherID { get; set; } public int? MotherID { get; set; } public int? MotherID { get; set; } etc.

Answer 2

This is a known issue, and you are not the first to encounter it. From what I heard, they are working on the best solution in the upcoming WCF version, however at the moment from my experience you are much better off creating DataContracts that represent the data that will be sent over the wire, thereby changing the data structure to remove the circular reference.

I know this is a pain, but there are other advantages in that you will most likely want to make other changes to the structures that your customers consume anyway, rather than letting them play with objects, as they exist in your db

Answer 3

I had the same problem, but I solved it using the advice in this Entity Framework Code First answer - two foreign keys from the same table , which works better than changing the type of key columns to optional.