Entity Framework: table without primary key

Question

I have an existing DB with which I would like to build a new app using EF4.0

Some tables do not have primary keys defined so that when I create a new Entity Data Model, I get the following message: "The table/view TABLE_NAME does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it".

If I want to use them and modify data, must I necessarily add a PK to those tables, or is there a workaround so that I don't have to?

Answer 1

THIS SOLUTION WORKS

You do not need to map manually even if you dont have a PK. You just need to tell the EF that one of your columns is index and index column is not nullable.

To do this you can add a row number to your view with isNull function like the following

select 
    ISNULL(ROW_NUMBER() OVER (ORDER BY xxx), - 9999) AS id
from a

ISNULL(id, number) is the key point here because it tells the EF that this column can be primary key

Answer 2

This is just an addition to @Erick T's answer. If there is no single column with unique values, the workaround is to use a composite key, as follows:

[Key]
[Column("LAST_NAME", Order = 1)]
public string LastName { get; set; }

[Key]
[Column("FIRST_NAME", Order = 2)]
public string FirstName { get; set; }

Again, this is just a workaround. The real solution is to fix the data model.

Answer 3

EF does not require a primary key on the database. If it did, you couldn't bind entities to views.

You can modify the SSDL (and the CSDL) to specify a unique field as your primary key. If you don't have a unique field, then I believe you are hosed. But you really should have a unique field (and a PK), otherwise you are going to run into problems later.

Erick

Answer 4

The above answers are correct if you really don't have a PK.

But if there is one but it is just not specified with an index in the DB, and you can't change the DB (yes, i work in Dilbert's world) you can manually map the field(s) to be the key.

Answer 5

I`m very happy my problem is solved.

Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist

and do this : Look below that line and find the tag. It will have a big ol' select statement in it. Remove the tag and it's contents..

now without changing DB T I can insert to a table which has not PK

thanks all and thank Pharylon

Answer 6

From a practical standpoint, every table--even a denormalized table like a warehouse table--should have a primary key. Or, failing that, it should at least have a unique, non-nullable index.

Without some kind of unique key, duplicate records can (and will) appear in the table, which is very problematic both for ORM layers and also for basic comprehension of the data. A table that has duplicate records is probably a symptom of bad design.

At the very least, the table should at least have an identity column. Adding an auto-generating ID column takes about 2 minutes in SQL Server and 5 minutes in Oracle. For that extra bit of effort, many, many problems will be avoided.