CODEWITHSUNDEEP
c#entity-frameworkforeign-keysentity-framework-6code-first
Ali Mottaghi Pour
Ali Mottaghi Pour

Reputation: 297

Entity Framework: Foreign Key in code first

What is wrong in my code that i get below error:

Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values

Code:

Class Food:

public class Food
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public short Id { get; set; }

    //some Property
    public string Name { get; set; }

    public virtual ICollection<Person> Persons { get; set; }
}

Class Person:

public class Person
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    //some Property
    public string FirstName { get; set; }

    [ForeignKey("BestFoodId")]
    public Food BestFood { get; set; }
    public short BestFoodId { get; set; }

    public virtual ICollection<Food> FavoriteFoods { get; set; }
}

Seed Method:

 protected override void Seed(MyContext context)
    {
        Food food1 = new Food() { Name = "foo1" };
        Food food2 = new Food() { Name = "foo2" };
        Food food3 = new Food() { Name = "foo3" };

        context.Persons.AddOrUpdate(new Person()
        {
            FirstName = "Jack",
            BestFood = food2,
            FavoriteFoods = new List<Food>() { food1, food2, food3 }
        });

    }

Upvotes: 1

Views: 2264

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109080

Cause of the error: confused associations

This happens because Entity Framework, by convention, assumes that the inverse property of Person.BestFoodId is Food.Persons. Stated differently: Person.BestFood and Food.Persons are assumed to be the two ends of a one-to-many association, having Person.BestFoodId as foreign key.

You can verify that by adding an [InverseProperty] attribute to BestFood:

public class Person
{
    ...
    [ForeignKey("BestFoodId")]
    [InverseProperty("Persons")]
    public Food BestFood { get; set; }
    ...
}

This causes the same error.

This error --no valid ordering-- always indicates a chicken-and-egg problem. In your case, EF tries to insert the foods, which need the generated Id of the inserted person as foreign key, while the inserted person needs the generated Id of the inserted foo2 food.

Solution: explicitly mapped association

In reality, Person and Food have two associations:

  • 1-n: Food can be BestFood of n people.
  • n-m: n Foods can be the FavoriteFoods of m people.

In your model, BestFood doesn't have an inverse property, which could have been something as ...

public virtual ICollection<Person> BestFoodOf { get; set; }

... but it isn't necessary and because it's missing, it obscures how EF infers the associations.

You can fix this by explicitly mapping the associations, for instance in the OnModelCreating override of your DbContext subclass:

modelBuilder.Entity<Person>()
            .HasRequired(p => p.BestFood)
            .WithMany() // No inverse property
            .HasForeignKey(p => p.BestFoodId)
            //.WillCascadeOnDelete(false)
            ;

modelBuilder.Entity<Person>()
            .HasMany(p => p.FavoriteFoods)
            .WithMany(f => f.Persons)
            .Map(m => m.MapLeftKey("PersonId")
                       .MapRightKey("FoodId")
                       .ToTable("PersonFavoriteFood"));

I have commented out WillCascadeOnDelete(false). You either have to add this line, or add ...

modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

... to prevent multiple cascaded delete paths (a SQL Server restrictions).

Having this in place, EF knows how to determine a valid ordering for the inserts: it will will first insert the foods, then insert the person (using the generated foo2 Id as foreign key) and then the junction records in the PersonFavoriteFood table.

Upvotes: 5

ˈvɔlə
ˈvɔlə

Reputation: 10242

Looks like you have a circular dependency.

Answers are here:

Optional improvements:

  • You should declare your navigation property as virtual!

  • If you are using C# 6.0 or above, change your [ForeignKeyAttribute] Data Annotation definition to [ForeignKey([nameof(BestFoodId))] to avoid errors with hard coded property names. nameof is a really cool compiler feature! :)

Upvotes: 1

Related Questions