[.net] Entity Framework: table without primary key

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?

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

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.

Update to @CodeNotFound's answer.

In EF Core 3.0 DbQuery<T> has been deprecated, instead you should use Keyless entity types which supposedly does the same thing. These are configured with the ModelBuilder HasNoKey() method. In your DbContext class, do this

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<YourEntityType>(eb =>
        {
            eb.HasNoKey();
        });

}

There are restrictions though, notably:

• Are never tracked for changes in the DbContext and therefore are never inserted, updated or deleted on the database.
• Only support a subset of navigation mapping capabilities, specifically:
  • They may never act as the principal end of a relationship.
  • They may not have navigations to owned entities
  • They can only contain reference navigation properties pointing to regular entities.
  • Entities cannot contain navigation properties to keyless entity types.

This means that for the question of

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?

You cannot modify data this way - however you can read. One could envision using another way (e.g. ADO.NET, Dapper) to modify data though - this could be a solution in cases where you rarely need to do non-read operations and still would like to stick with EF Core for your majority cases.

Also, if you truly need/want to work with heap(keyless) tables - consider ditching EF and use another way to talk to your database.

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.

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.

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 });
        ...
     }
}

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; }

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

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

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... :)

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.

In EF Core 5.0, you will be able to define it at entity level also.

[Keyless]
public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public int Zip { get; set; }
}

Reference: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#use-a-c-attribute-to-indicate-that-an-entity-has-no-key

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.

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

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

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.

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.