How do I eliminate Error 3002?

Question

Say I have the following table definitions in SQL Server 2008:

CREATE TABLE Person
(PersonId INT IDENTITY NOT NULL PRIMARY KEY,
 Name VARCHAR(50) NOT NULL,
 ManyMoreIrrelevantColumns VARCHAR(MAX) NOT NULL)

CREATE TABLE Model
(ModelId INT IDENTITY NOT NULL PRIMARY KEY,
 ModelName VARCHAR(50) NOT NULL,
 Description VARCHAR(200) NULL)

CREATE TABLE ModelScore
(ModelId INT NOT NULL REFERENCES Model (ModelId),
 Score INT NOT NULL,
 Definition VARCHAR(100) NULL,
 PRIMARY KEY (ModelId, Score))

CREATE TABLE PersonModelScore
(PersonId INT NOT NULL REFERENCES Person (PersonId),
 ModelId INT NOT NULL,
 Score INT NOT NULL,
 PRIMARY KEY (PersonId, ModelId),
 FOREIGN KEY (ModelId, Score) REFERENCES ModelScore (ModelId, Score))

The idea here is that each Person may have only one ModelScore per Model, but each Person may have a score for any number of defined Models. As far as I can tell, this SQL should enforce these constraints naturally. The ModelScore has a particular "meaning," which is contained in the Definition. Nothing earth-shattering there.

Now, I try translating this into Entity Framework using the designer. After updating the model from the database and doing some editing, I have a Person object, a Model object, and a ModelScore object. PersonModelScore, being a join table, is not an object but rather is included as an association with some other name (let's say ModelScorePersonAssociation). The mapping details for the association are as follows:

- Association
  - Maps to PersonModelScore
    - ModelScore
        ModelId : Int32       <=>  ModelId : int
        Score : Int32         <=>  Score : int
    - Person
        PersonId : Int32      <=>  PersonId : int

On the right-hand side, the ModelId and PersonId values have primary key symbols, but the Score value does not.

Upon compilation, I get:

Error 3002: Problem in Mapping Fragment starting at line 5190: Potential runtime violation of table PersonModelScore's keys (PersonModelScore.ModelId, PersonModelScore.PersonId): Columns (PersonModelScore.PersonId, PersonModelScore.ModelId) are mapped to EntitySet ModelScorePersonAssociation's properties (ModelScorePersonAssociation.Person.PersonId, ModelScorePersonAssociation.ModelScore.ModelId) on the conceptual side but they do not form the EntitySet's key properties (ModelScorePersonAssociation.ModelScore.ModelId, ModelScorePersonAssociation.ModelScore.Score, ModelScorePersonAssociation.Person.PersonId).

What have I done wrong in the designer or otherwise, and how can I fix the error?

Many thanks!

Sequence

You should accept Shan's answer. It is correct and saved my bacon. – Circinate

Answer 1

Very late to your question, I had the same issue and discovered that the entity framework designer had identified my "ScoreId" column (relative to your PersonModelScore table) as a primary key column. I changed my setting to false for my ScoreId, and all worked well afterward.

Mondragon

This fix worked for me. One table had two primary keys when it should have only had one. – Overalls

Same here. EF decided that all the not-null columns should be part of the primary key which they are not supposed to be. – Vltava

It took me a bit to figure out the steps for this, so adding here for clarity. Go to your .edmx file, right click on the background and select 'Mapping Details'. Click on the Table you need to edit the mappings of in your .edmx window and the details should appear in your new 'Mapping Details' window. Hit F4 to bring up the 'Properties' window (default location, bottom right of your VS display). Click on the 'Value/Property' in your 'Mapping Details' to change the Properties displayed, and from your Properties window you can now set the 'Entity Key' value to 'False'. – Quickman

Six years later and this answer still saved me. EF decided that an extra 3 columns needed to be part of the primary key. – Hazelwood

Better late to the party than never! – Mondragon

Answer 2

You can set single primary key in the Entity in order to avoid this error.Right Click on the Scalar Properties of the field in the Entity and disable Entity Key if there are many primary keys.

Yocum

Thank you! This solved for me, Error 1 Error 3002: Problem in mapping fragments starting at line 942:Potential runtime violation of table [tablename]'s keys ([entityname].Id): Columns ([entityname].Id) are mapped to EntitySet [entityset] properties ([tablename].Id) on the conceptual side but they do not form the EntitySet's key properties – Octarchy

Answer 3

You should create a single Identity key for each table.

ModelScore should have a ModelScoreId, PersonModelScore should have a PersonModelScoreId.

References between table should be a single field.

Io

Thank you for your suggestions. Now, how does this solution still preserve the integrity of the data model? Each Person is associated with no more than one of each Model, and the ModelScore must be a valid ModelScore for that Model. So, I think PersonModelScore would either have to have both a ModelId and a ModelScoreId, which is redundant and requires verification, or just have ModelScoreId, which requires verification that there are not multiple ModelScores per Model per Person. Neither way seems to work very well, in my opinion. Is this just a limitation of Entity Framework, then? – Sequence

Answer 4

Your PersonModelScore table should define an Id column that is identity and primary key, then you should create a unique key on PersonId, ModelId

as for Error 3002, i had the same problem ALL my field had been marked Entity key by EF

Telfer

Answer 5

"Go to your .edmx file, right click on the background and select 'Mapping Details'. Click on the Table you need to edit the mappings of in your .edmx window and the details should appear in your new 'Mapping Details' window. Open Properties tab (Hit F4 to bring up the 'Properties' if not open) Click on the 'Value/Property' in your 'Mapping Details' to change the Properties displayed, and from your Properties window you can now set the 'Entity Key' value to 'False'. – Chris Paton Oct 4 '14 at 18:54"

This worked for me - Thanks This is now part of my workflow when using EF Database First. And we have a task out to update the data model.

Gunny