Multiplicity constraint violated SQL Server 2008 - CodeFirst

Question

I'm working to solve a very tedious problem. I have a class called Nation and a class called NationAlly

public class Nation   
{
    public int ID {get; set;}
    public int name {get;set;}
    public List<NationAlly> NationAllies {get;set;}
}

public class NationAlly
{
    public int ID {get; set;}
    public int level {get;set;}
    public Nation toNation {get;set;}
}

I'm using EF 4 and CodeFirst with a DbContext called NationsDB to manage my database on SQL Server 2008. If I create a new object of type Nation and I try to call nationsDB.SaveChanges, I got the following exception:

"Multiplicity constraint violated. The role 'NationAlly_toNation_Target' of the relationship 'CodeFirstNamespace.NationAlly_toNation' has multiplicity 1 or 0..1."

I tried to save a Nation with NationAllies field null and this exception is not thrown, the nation table in the database gets all the correct values.

In my database the table Nation has 2 fields: ID(primary key), name The table NationAlly has 3 fields: ID(primary key), level, NationID The two tables are linked with a relationship where NationAlly.NationID is foreign key and Nation.ID is primary key.

Isn't strange? In my eyes the table NationAlly should have a field called NationID1 and another called NationID2 to create the "relationship" between a nation and a list of other nations.

What did I do wrong?

Answer 1

You are perhaps a victim of the EF Code-First mapping conventions which create automatically a relationship between NationAllies and toNation you don't want to have.

If I understand you correctly (but I am not 100 percent sure, if I do), you actually want to have two relationships and you have exposed only one end of the relationship in each of the entities. So, NationAllies does NOT point to toNation but to an "invisible" Owner nation in your NationAlly entity.

If that is the case you need to explicitly overwrite the convention mappings. In the Fluent API of EF 4.1 this could look like:

public class MyContext : DbContext
{
    public DbSet<Nation> Nations { get; set; }
    public DbSet<NationAlly> NationAllies { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Nation>()
            .HasMany(n => n.NationAllies)
            .WithRequired()
            .Map(conf => conf.MapKey("OwnerID"))
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<NationAlly>()
            .HasRequired(a => a.toNation)
            .WithMany()
            .Map(conf => conf.MapKey("NationID"))
            .WillCascadeOnDelete(false);
    }
}

This mapping would create the two foreign keys OwnerID and NationID in the NationAllies table, both pointing to the primary key ID in the Nations table.

Edit

Here is the application I have tested with:

• Create a new Console App in VS2010 / .NET 4.0, name it "NationsApp"
• Add a reference to "EntityFramework.dll"
• Clear the content of "Program.cs" and paste instead the following in:

Content of Program.cs:

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

namespace NationsApp
{
    public class Nation
    {
        public int ID { get; set; }
        public int name { get; set; }
        public List<NationAlly> NationAllies { get; set; }
    }

    public class NationAlly
    {
        public int ID { get; set; }
        public int level { get; set; }
        public Nation toNation { get; set; }
    }

    public class NationsContext : DbContext
    {
        public DbSet<Nation> Nations { get; set; }
        public DbSet<NationAlly> NationAllies { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Nation>()
                .HasMany(n => n.NationAllies)
                .WithRequired()
                .Map(conf => conf.MapKey("OwnerID"))
                .WillCascadeOnDelete(false);

            modelBuilder.Entity<NationAlly>()
                .HasRequired(a => a.toNation)
                .WithMany()
                .Map(conf => conf.MapKey("NationID"))
                .WillCascadeOnDelete(false);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new NationsContext())
            {
                try
                {
                    // We have three Nations and two Allies
                    Nation nation1 = new Nation() {
                        NationAllies = new List<NationAlly>() };
                    Nation nation2 = new Nation() {
                        NationAllies = new List<NationAlly>() };
                    Nation nation3 = new Nation() {
                        NationAllies = new List<NationAlly>() };
                    NationAlly ally1 = new NationAlly();
                    NationAlly ally2 = new NationAlly();

                    // Nation1 has two Allies
                    // (Nation1 is the "owner" of both Allies)
                    nation1.NationAllies.Add(ally1);
                    nation1.NationAllies.Add(ally2);

                    // toNation of ally1 refers to Nation2
                    ally1.toNation = nation2;
                    // toNation of ally2 refers to Nation3
                    ally2.toNation = nation3;

                    context.Nations.Add(nation1);
                    context.Nations.Add(nation2);
                    context.Nations.Add(nation3);

                    context.SaveChanges();
                }
                catch (Exception e)
                {
                    throw;
                }
            }
        }
    }
}

You can set a breakpoint on "throw" to watch possible exceptions in e in the debugger.

This creates a database called NationsApp.NationsContext if you are using SQL Server Express and don't have any further connection strings defined.

It gives two relationships Nation_NationAllies (FK is "OwnerID") and NationAlly_toNation (FK is "NationID"). All columns are non-nullable. The result in the DB is the following:

Answer 2

In case this helps someone getting this error... I was getting this message while doing queries rather than saving to the database. My data design:

public class Base {
    public int Id {get; set;}
}

public class Child {
    [Key][ForeignKey("Base")] public int Id {get; set;}

    public virtual Base Base {get; set;}

    public Child() {
        Base = new Base();
    }
}

The problem was in the constructor. Turns out EF4.1 doesn't like when you initialize associations there! I removed that constructor and things started working again.