Entity Framework + Composite Keys with Duplicates

Question

I am trying to store a matrix in a database using a code-first model with Entity Framework. I have created an object for each element in the matrix. I want to be able to search both rows and columns so I was hoping to have both my RowID and ColID as Key. Using composite keys I am still getting the error:

"Trojan.Models.Matrix_Element: : EntityType 'Matrix_Element' has no key defined. Define the key for this EntityType.

default_Matrix: EntityType: EntitySet 'default_Matrix' is based on type 'Matrix_Element' that has no keys defined."

I am guessing that this is because there are duplicate values for RowID and ColID. It is a matrix so every pair (RowID, ColID) is unique but I am guessing that because there are multiple entries per row and multiple entries per column it considers there being elements with the same PK.

Is there a way to identify an element ONLY by the pair of keys? Or am I forced to create an ElementID attribute and use that as a PK?

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Trojan.Models
{
    public class Matrix_Element
    {
        [Key, Column(Order = 0)]
        public int RowID;
        [Key, Column(Order = 1)]
        public int ColID;
        public int? cellValue;
        public string R;
    }
}

If it has duplicates, then it's not a key - a (primary) key **by definition** must be unique. So it your `(RowID, ColID)` cannot be unique - it cannot be used as a key - yes, you'll need a separate `ElementID` that **is** unique and therefore can be used as key — marc_s, Jul 06 '15 at 20:46
may be you need property, not variable: `public int RowID {get; set;}`: http://stackoverflow.com/questions/1180860/public-fields-versus-automatic-properties — tschmit007, Jul 06 '15 at 21:13
No you understood it correctly, `RowID` can have duplicates and `ColID` can have duplicates, but each pair `(RowID, ColID)` has to be unique (which it is in a matrix). Your mappings look fine too, but this error suggests that EF is ignoring them. Is your `Matrix_Element` class added as a DbSet to your DbContext? — Florian Haider, Jul 07 '15 at 07:57

score 0 · Answer 1 · answered Jul 07 '15 at 09:15

You need to use properties rather than fields. Change your class to this....

public class Matrix_Element
{
    [Key, Column(Order = 0)]
    public int RowID { get; set; }

    [Key, Column(Order = 1)]
    public int ColID { get; set; }

    public int? cellValue { get; set; }

    public string R { get; set; }
}

....and you will get a migration like this:

public override void Up()
{
    CreateTable(
        "dbo.Matrix_Element",
        c => new
            {
                RowID = c.Int(nullable: false),
                ColID = c.Int(nullable: false),
                cellValue = c.Int(),
                R = c.String(),
            })
        .PrimaryKey(t => new { t.RowID, t.ColID });//Success!

}

Entity Framework + Composite Keys with Duplicates

1 Answers1