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

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 2

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.

Answer 3

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 4

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

Answer 5

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://learn.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

Answer 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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.

Answer 14

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 15

You can have a composite key setup (similar to how VIEWS are done in EF), and apply both key and column order to the fields so that the combination is unique, EF doesn't need a PK (only useful, if doing insert, update or delete operations) to start with, here's an example of a recent implementation:

[Key]
[Column(Order = 0)]
public int NdfID { get; set; }

[Key]
[Column(Order = 1)]
public int? UserID { get; set; }

[Key]
[Column(Order = 2)]
public int ParentID { get; set; }

In this example, my userid field does contain nulls but with the combination of these three all rows are now unique.

Edited this after years :)

Answer 16

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.

Answer 17

I learned my lesson by working around it. The short answer is DO NOT work around it.

I used EF6 to read a table without a PK but having a compound key. Multiple rows with the same compound key would have the exactly same record. Essentially only one row has been read but used to fill all rows. Since there were million records and it only occurred for a relatively small amount of records which made it very difficult to find the issue.

Answer 18

We have a table without a unique ID column. Other columns were expected to create a composite key, but over time the data has sometimes not had values in all composite key columns.

Here is a solution using the .NET Entity Framework:

[Key]
[Column(Order = 1)]
public Guid FakeId { get; set; }
public ... other columns

And change the SQL to select this:

SELECT NEWID() as FakeId, ... other columns

Answer 19

Late answer here. If you can add a Key - you should. EntityFramework uses it internally. I've seen issues - If you have 2 or more identical rows, it'll only come back once. If that's not an option, You need to make an in-memory composite key. You can do this via the Fluent API in the DbContext or in the class mapping.

Option #1:
In your DbContext to make an in-memory composite key with the Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<YOUR_TABLE>()
            .HasKey(c => new { c.Column1, c.Column2, c.Column3, etc. });
}

Option #2:
Same thing but with attributes in EF Object Mapping.

 public class TableName
{
    [Key]
    [Column(Order = 0)]
    public int Column1 { get; set; }
    
    [Key]
    [Column(Order = 1)]
    public int? Column2 { get; set; }
    
    [Key]
    [Column(Order = 2)]
    public int Column3 { get; set; }
}

Option #3:
If you're in EF Core 3.0+ use the fluent API:

modelBuilder
    .Entity<YourEntityType>(eb =>
    {
        eb.HasNoKey();
    });

Option #5:
If you're in EF Core 5.0+ you can do it directly in the EF Object Mapping

[Keyless]
public class TableName
{
    [Key]
    [Column(Order = 0)]
    public int Column1 { get; set; }
    
    [Key]
    [Column(Order = 1)]
    public int? Column2 { get; set; }
    
    [Key]
    [Column(Order = 2)]
    public int Column3 { get; set; }
}

Answer 20

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