Composite Key in EF

Question

I cant seem to understand how EF deals with composite keys. When I try to 'add-migration Initial' the below code returns "The property 'QuestionID ' cannot be used as a key property on the entity QuestionQuestionTypesModel' because the property type is not a valid key type. Only scalar types, string and byte[] are supported key types."

I also tried to set annotations instead of overriding OnModelCreating. I used [Key, Column(Order = 0)]

Can anyone give me any clues on what Im doing wrong? Or explain whats happening to better understand the problem at hand?

          public class QuestionModel
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public Guid ID { get; set; }

        [Required]
        [StringLength(250)]
        public string Question { get; set; }
   }


  public class QuestionTypeModel
    {
        [Key]
        [HiddenInput(DisplayValue = false)]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public Guid ID { get; set; }

        [Required]
        [StringLength(250)]
        public string TypeName { get; set; }
    }


  public class QuestionQuestionTypesModel
    {
        public virtual QuestionModel QuestionID {get;set;}
        public virtual QuestionTypeModel QuestionTypeID { get; set; }
    }

public class InnuendoContext : DbContext
    {

      public IContext() : base("DefaultConnection")
        {
        }

        public DbSet<QuestionTypeModel> QuestionTypes { get; set; }
        public DbSet<QuestionModel> Questions { get; set; }
        public DbSet<QuestionQuestionTypesModel> QuestionQuestionTypes { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
            modelBuilder.Entity<QuestionQuestionTypesModel>().HasKey(a => new { a.QuestionID, a.QuestionTypeID });
        }
}

This sample code would not compile. `A` and `B` are not defined unless you say what they exactly are. Please, update your sample with working code. If not, I'll ask to close this question. — JotaBe, Aug 28 '15 at 09:02
I still cant see why the previous code wouldnt compile. Anyways, Ive updated the code. Hopefully it's clearer now. — user1203996, Aug 28 '15 at 09:17
A = QuestionModel B = QuestionTypeModel C = QuestionQuestionTypesModel — user1203996, Aug 28 '15 at 09:23
Looking at the updated code, im kinda confused now. What are the use of QuestionQuestionTypesModel, it looks as though you just want to give a question a (one) questiontype. — Lars Nielsen, Aug 28 '15 at 09:27
Its a many to many relationship. We have many questions. We have many QuestionTypes. They linked in QuestionQuestionTypesModel. — user1203996, Aug 28 '15 at 09:33

Jaime Mendes · Accepted Answer · 2015-08-28T09:48:52.487

You have to create the properties required for the table which are also the the foreign keys of the system. By setting this structure:

public class QuestionQuestionTypesModel
{
    [Key, Column(Order = 1), ForeignKey("Question")]
    public Guid QuestionID { get; set; }
    [Key, Column(Order = 2), ForeignKey("QuestionType")]
    public Guid QuestionTypeID { get; set; }

    public virtual QuestionModel Question { get; set; }
    public virtual QuestionTypeModel QuestionType { get; set; }
}

You get this migration:

public override void Up()
{
    CreateTable(
        "dbo.QuestionModel",
        c => new
        {
            ID = c.Guid(nullable: false, identity: true),
            Question = c.String(nullable: false, maxLength: 250),
        })
        .PrimaryKey(t => t.ID);

    CreateTable(
        "dbo.QuestionTypeModel",
        c => new
        {
            ID = c.Guid(nullable: false, identity: true),
            TypeName = c.String(nullable: false, maxLength: 250),
        })
        .PrimaryKey(t => t.ID);

    CreateTable(
        "dbo.QuestionQuestionTypesModel",
        c => new
            {
                QuestionID = c.Guid(nullable: false),
                QuestionTypeID = c.Guid(nullable: false),
            })
        .PrimaryKey(t => new { t.QuestionID, t.QuestionTypeID })
        .ForeignKey("dbo.QuestionModel", t => t.QuestionID, cascadeDelete: true)
        .ForeignKey("dbo.QuestionTypeModel", t => t.QuestionTypeID, cascadeDelete: true)
        .Index(t => t.QuestionID)
        .Index(t => t.QuestionTypeID);
}

Update Just saw your comment. If you have just a many-to-many relationship and you don't need any other attribute, you can do this:

public class QuestionModel
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Guid ID { get; set; }

    [Required]
    [StringLength(250)]
    public string Question { get; set; }
    //One question has many QuestionTypes
    public virtual ICollection<QuestionTypeModel> QuestionTypes { get; set; }
}


public class QuestionTypeModel
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public Guid ID { get; set; }

    [Required]
    [StringLength(250)]
    public string TypeName { get; set; }
    //One QuestionType has many Questions
    public virtual ICollection<QuestionModel> Questions { get; set; }
}

This will produce the same migration but makes your data layer clear.

This explains alot. Thank you. Your code and explanation are very clear Thank you! — user1203996, Aug 28 '15 at 09:45

score 0 · Answer 2 · answered Aug 28 '15 at 09:38

modelBuilder.Entity<QuestionQuestionTypesModel>().HasKey(a => new { a.QuestionID, a.QuestionTypeID });

QuestionID and QuestionTypeID are both navigation properties and therefore can't be used as primary keys. It's as the Error message suggests: only these Datatypes are supported as primary keys (can be converted to key columns in the supported databases), unfortunately, QuestionModel and QuestionTypeModel are none of these. Add Guid key values to match the key columns of QuestionModel and QuestionTypeModel.

Composite Key in EF

2 Answers2