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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Composite keys can also be done with Entity Framework Fluent API    public class MyModelConfiguration   EntityTypeConfiguration lt MyModel gt         public MyModelConfiguration                  ToTable  MY MODEL TABLE            HasKey x   gt  new   x SourceId  x StartDate  x EndDate  x GmsDate

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I m very happy my problem is solved   Unable to update the EntitySet - because it has a DefiningQuery and no  lt UpdateFunction gt  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

User · Answer

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

User · Answer

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

User · Answer

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 lt T gt  instead of DbSet lt T gt  like below  Assuming your table name is MyTable   public DbQuery lt MyTable gt  MyTables   get  set

User · Answer

Update to  CodeNotFound s answer   In EF Core 3 0 DbQuery lt T gt  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 lt YourEntityType gt  eb   gt                        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

User · Answer

Change the Table structure and add a Primary Column  Update the Model Modify the  EDMX file in XML Editor and try adding a New Column under  tag for this specific table  WILL NOT WORK  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

[.net] Entity Framework: table without primary key

Examples related to .net

Examples related to entity-framework