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

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?

For those reaching this question and are using Entity Framework Core, you no longer need to necessarily add a PK to thoses tables or doing any workaround. Since EF Core 2.1 we have a new feature Query Types

Query types must be used for:

• Serving as the return type for ad hoc FromSql() queries.
• Mapping to database views.
• Mapping to tables that do not have a primary key defined.
• Mapping to queries defined in the model.

So in your DbContext just add the following property of type DbQuery<T> instead of DbSet<T> like below. Assuming your table name is MyTable:

public DbQuery<MyTable> MyTables { get; set; }

Answer 2

We encountered this problem as well, and while we had a column that had nulls, what was important was that we had a dependent column that did not have nulls and that the combination of these two columns was unique.

So to quote the response given by Pratap Reddy, it worked fine for us.

Answer 3

This maybe to late to reply...however...

If a table does't have a primary key then there are few scenarios that need to be analyzed in order to make the EF work properly. The rule is: EF will work with tables/classes with primary key. That is how it does tracking...

Say, your table 1. Records are unique: the uniqueness is made by a single foreign key column: 2. Records are unique: the uniqueness are made by a combination of multiple columns. 3. Records are not unique (for the most part*).

For scenarios #1 and #2 you can add the following line to DbContext module OnModelCreating method: modelBuilder.Entity().HasKey(x => new { x.column_a, x.column_b }); // as many columns as it takes to make records unique.

For the scenario #3 you can still use the above solution (#1 + #2) after you study the table (*what makes all records unique anyway). If you must have include ALL columns to make all records unique then you may want to add a primary key column to your table. If this table is from a 3rd party vendor than clone this table to your local database (overnight or as many time you needed) with primary key column added arbitrary through your clone script.

Answer 4

The error means exactly what it says.

Even if you could work around this, trust me, you don't want to. The number of confusing bugs that could be introduced is staggering and scary, not to mention the fact that your performance will likely go down the tubes.

Don't work around this. Fix your data model.

EDIT: I've seen that a number of people are downvoting this question. That's fine, I suppose, but keep in mind that the OP asked about mapping a table without a primary key, not a view. The answer is still the same. Working around the EF's need to have a PK on tables is a bad idea from the standpoint of manageability, data integrity, and performance.

Some have commented that they do not have the ability to fix the underlying data model because they're mapping to a third-party application. That is not a good idea, as the model can change out from under you. Arguably, in that case, you would want to map to a view, which, again, is not what the OP asked.