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

Having a useless identity key is pointless at times. I find if the ID isn't used, why add it? However, Entity is not so forgiving about it, so adding an ID field would be best. Even in the case it's not used, it's better than dealing with Entity's incessive errors about the missing identity key.

Answer 2

1. Change the Table structure and add a Primary Column. Update the Model
2. Modify the .EDMX file in XML Editor and try adding a New Column under tag for this specific table (WILL NOT WORK)
3. Instead of creating a new Primary Column to Exiting table, I will make a composite key by involving all the existing columns (WORKED)

Entity Framework: Adding DataTable with no Primary Key to Entity Model.

Answer 3

I think this is solved by Tillito:

Entity Framework and SQL Server View

I'll quote his entry below:

We had the same problem and this is the solution:

To force entity framework to use a column as a primary key, use ISNULL.

To force entity framework not to use a column as a primary key, use NULLIF.

An easy way to apply this is to wrap the select statement of your view in another select.

Example:

SELECT
  ISNULL(MyPrimaryID,-999) MyPrimaryID,
  NULLIF(AnotherProperty,'') AnotherProperty
  FROM ( ... ) AS temp

answered Apr 26 '10 at 17:00 by Tillito

Answer 4

In my case I had to map an entity to a View, which didn't have primary key. Moreover, I wasn't allowed to modify this View. Fortunately, this View had a column which was a unique string. My solution was to mark this column as a primary key:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[StringLength(255)]
public string UserSID { get; set; }

Cheated EF. Worked perfectly, no one noticed... :)

Answer 5

The table just needs to have one column that does not allow nulls

Answer 6

Composite keys can also be done with Entity Framework Fluent API

public class MyModelConfiguration : EntityTypeConfiguration<MyModel>
{
     public MyModelConfiguration()
     {
        ToTable("MY_MODEL_TABLE");
        HasKey(x => new { x.SourceId, x.StartDate, x.EndDate, x.GmsDate });
        ...
     }
}