Removing false Pkeys from store model?

Question

I have a table with a single primary key defined for the table in the database.  Using DB first methodology.  Performed a refresh from DB, but the problem is EF still also includes another column as a second Pkey of the table.  This is incorrect.  There is only one column defined as the pkey in the database.  As far as I have been able to find in the years fighting with EF, the store model is only manageable through xml edits that will often get blown away when refreshing from the DB to add additional tables(even when nothing has changed structurally with tables already in the model).

I am unable to validate the model and run my program because this false pkey is not mapped in the conceptual type.  The reason it is not mapped, is because it is used in a relationship as a FK, therefore even if I do put it in the conceptual type and map it, I then get a new error dealing with the fact that it is mapped in both a conceptual property and the conceptual relationship.

How can I work around this problem?

Answer 1

I am able to fix this by adding the property in the conceptual side, mapping it in the mapping table, attempting to validate, which gives me the error about multiple mappings of the conceptual side property, and then I delete the conceptual side property.  This process somehow causes the pkey designation of the property to be removed, and then I delete the property from the conceptual model and it validates.  I have to do this dance for all the simlarly affected columns every time I do a refresh from the DB.  Maybe this is the ritual I must do to appease the gods in the computer so that they will let me access my data.

Answer 2

Hi AaronLST,

Welcome to MSDN Forum.

Do you mean you use database first, and there's only one primary in the table, but two primary keys apears in the conceptual model? I don't think it is possible. Entity Framework are one to one mapping, it will not create a primary key automatically for you. Could you please clarify more clearly about the situation? By the way, glad to hear that you have solved the problems.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 3

I was getting the same thing about an hour ago.  I had double clicked on the edmx to get me where is shows all the tables.  One of my files, all the fields I had set to NO NULL, had the Entity set to TRUE.   

It was not happening on all my tables, so I had to see what the difference is.  I simply deleted the tables from the edmx view that where posted bad,  then I saved it, rebuilt my project. All good. Closed the edmx, went back into it.

Then chose the update from database, and click tables, and click add, and added back the ones that where done wrong. This time it added them back with the single ENTITY as true, and all the others false, and it worked.

Right before this, I had went into it and selected all, and deleted them, then readded.  Before I readded, and changed the PK primary key on a table to just the single field.  When I readded the time that messed it up, it was like it was reading a OLD PK value from the table, and not the updated and saved value.

Thank you,

Frank C :)

Answer 4

Hi AaronLST,

Welcome to MSDN Forum.

Do you mean you use database first, and there's only one primary in the table, but two primary keys apears in the conceptual model? I don't think it is possible. Entity Framework are one to one mapping, it will not create a primary key automatically for you. Could you please clarify more clearly about the situation? By the way, glad to hear that you have solved the problems.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Allen, just like Frankfort, I also have trouble reproducing these scenarios consistently.  One scenario I am able ot reproduce consistently is if my table has no pkeys defined, then EF database first assumes all of the not null integers are a composite pkey and sets them all as pkey.  The problem is EF is supposed to be a mapping framework, and this should be a fairly simple scenari to get around.  I just tell EF that it's "guess" is wrong and remove the pkey designation from the effected entities.

The flaw in EF is that you can only do this for the conceptual model.  You have completely no control over correcting these incorrect guesses in the store model.  Really EF should let you either 1) correct the incorrect guesses in the store model, and without doing xml editing that will get blown away on the next Update from Database, 2) Leave the store model alone, but give the changes in the conceptual model precedence. 

Recognizing that EF is supposed to be an ORM, my expectation is that the mapping part can handle something as simple as me saying "Hey, our database which I have no control over doesn't contain all the constraints that it should, so I'm gonna tell in the mapping+conceptual layers that this table has this column as a pkey, and has this relationship with these tables".  This should be fairly simple and straightforward and is not a challenge with other ORMs.  I really am a big fan of the concept of EF, but the way MS has implemented it is all wrong.  I cringe everytime I have to go into an edmx because I know I will be banging my head against the wall over the simplest of things.

Answer 5

Hi AaronLST,

You don't have a primary key in database, but conceptual model need a primary key, so EF couldn't specify which column is the primary key. I suggest you to add a primary key to the database table, because EF need a key of the conceptual model. If there's no primary key of the database table, just add key to conceptual model, when you do a query, the result very likely isn't what you want.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 6

You don't have a primary key in database, but conceptual model need a primary key, so EF couldn't specify which column is the primary key.

You have restated my own observation.  What I am saying is I should be able to provide EF a hint as to what the PK is int he store model.

I suggest you to add a primary key to the database table, because EF need a key of the conceptual model.

This is usually not possible in the scenarios I deal with.  The source DB is sometimes a thirdparty DB for which I have no authority or right to modify, and/or it doesn't make sense to modify the source DB because future updates to the third party application/DB will negate those edits.  Thus an ORM is the appropriate tool for the job, allowing me to map the flawed DB model to a more ideal and correct model.  If every DB were modeled perfectly, then there would be no need for a conceptual or mapping layer.

If there's no primary key of the database table, just add key to conceptual model, when you do a query, the result very likely isn't what you want.

You have misread the scenario.  You have suggested I add the key to the conceptual model, however that makes no sense as I indicated the key is already part of the conceptual and store models as a result of doing an Update from Database.  The key is already added automatically in the conceptual model.  The problem is, it flags all non null columns as a composite pkey, and there is no straightforward way to provide a hint to EF to remove those other false pkey designations from the store model.  Thus you have to go through the elaborate dance I described above in the conceptual model property as pkey, because then the validation will fail and you will be unable to compile.  You have to go through the elaborate dance I described previously each time you do an update from DB.  I am doing a basic model type per table mapping.  As I add more tables to my model, the length of this dance lengthens, as I must repeat it for all previously effected tables everytime I do an Update from DB.

Answer 7

Hi AaronLST,

Yes, I understand your meaning, it's really frustrated. Because EF need a Entity Key to distinguish entities. If there's no primary key in the database, It can't infer to it. So, they treat all the not null properties as primary key. It means, two entities have the same values of the properties are considered as the same entity. I'm afraid it is by design. Below is the document of it, please refer to it.

http://msdn.microsoft.com/en-us/library/dd163156.aspx

The document also provide a solution about adding no-primary key table or view to the EDMX file. It need you to modify the CSDL, SSDL and MSL files mannually.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 8

It need you to modify the CSDL, SSDL and MSL files mannually.

Those changes get blown away the next time you refresh from the database.