entity framework composite key not allowing duplicate data

Question

I am using entity framework code first to design a SQL database and "SEED" method to populate the database with initial data. Following are two models with one to many relationship. "Foo" can have many "FooSection"

public class Foo {
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int FooId {get; set;}
   // Some more properties.
   // Navigation collection
   public virtual ICollection<FooSection> fooSections {get; set;}
}

public class FooSection {
   // Has composite key
   [ForeignKey("foo"), Column(Order=1)]
   public int FooId {get; set;}       
   [Key, Column(Order=2)]
   public string SectionName {get; set;}
   // Some more properties
   // Navigation property
   public virtual Foo foo {get; set;}
}

Say first instance of "Foo" with FooId = 1, has 2 "FooSection" and second instance of "Foo" with FooId = 2, has 1 "FooSection". So my seed method looks like this -

 protected override void Seed(PDI.Cloud.Models.ApplicationDbContext context)
 {
    // First add data for "Foo"
    var FooList = new List<Foo>();
    if(!(context.Foos.Any()))
    {
        // First instance of Foo
        FooList.Add( 
           new Foo{
             // Assign values to properties here
           }
        );
        // Second instance of Foo
        FooList.Add( 
           new Foo{
             // Assign values to properties here
           }
        );
       FooList.ForEach(f => context.Foos.AddOrUpdate(f));
       context.SaveChanges();

       // Get info of "FooSection"
       var fooSectList = getFooSectList(context); 
       // Assign "FooSection"s to respective "Foo".
       FooList[0].fooSections.Add(fooSectList[0]); // Section 1 for Foo with id = 1
       FooList[0].fooSections.Add(fooSectList[1]); // Section 2 for Foo with id = 1
       FooList[1].fooSections.Add(fooSectList[2]); // Section 1 for Foo with id = 2
       Context.SaveChanges();
    }
 }

private List<FooSection>getFooSectList(PDI.Cloud.Models.ApplicationDbContext context)
    {
       var FooSectList = new List<FooSection>();
       if(!(context.FooSections.Any()))
       {
        // 1st FooSection for Foo with FooId = 1
        FooSectList.Add( 
           new FooSection{
             FooId = 1,
             SectionName = "Sect1"
           }
        );
        // 2nd FooSection for Foo with FooId = 1
        FooSectList.Add( 
           new FooSection{
             FooId = 1,
             SectionName = "Sect2"
           }
        );
        // 1st FooSection for Foo with FooId = 2
        FooSectList.Add( 
           new FooSection{
             FooId = 2,
             SectionName = "Sect1"
           }
        );
       FooSectList.ForEach(f => context.FooSections.AddOrUpdate(f));
       context.SaveChanges();
      }
      return FooSectList;
    }

When I try to run the seed method, it gives me SQLException "Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object. The duplicate key value is (0, Sect1)"

Am I missing something here? Because I think for composite keys as far as combination is unique, I should not get such error.

I would appreciate any help.

Thanks.

sstan sstan · Accepted Answer · 2016-07-12T16:31:39

I think you are expecting FooSection to have a composite primary key of FooId, SectionName. However, the way you have it now, your primary key is SectionName only.

You'll have to add the Key attribute to the FooId property to turn it into a composite primary key:

[Key, ForeignKey("foo"), Column(Order=1)]
public int FooId {get; set;}

entity framework composite key not allowing duplicate data

1 Answers