0

First of all, my question is based on the answer that was given here.

I have a table where I didn't create a Primary Key. This table is used for logging purposes. While adding a new record to this table using EntityFramework (v6.0) I get the following error:

An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code Additional information: Unable to update the EntitySet 'Status' because it has a DefiningQuery and no element exists in the element to support the current operation.

After some searches I found out that this error occurs because my table does not have a Primary Key. I know I can fix this error by adding a Primary Key to my table. But I want to know why.

Question: If it is not necessary for my design to use a Primary Key in my table, why isn't it possible for the EntityFramework to work with this?

Community
  • 1
  • 1
Robin Gordijn
  • 683
  • 1
  • 4
  • 16
  • Why is it necessary for you to have a name or a SIN? – Will Marcouiller Jan 15 '15 at 15:29
  • 1
    @WillMarcouiller bad question - names are not unique! – Vladimirs Jan 15 '15 at 15:29
  • 2
    Why does my account at the bank have an account number? – hatchet - done with SOverflow Jan 15 '15 at 15:30
  • @Vladimirs: You got me! ;-) Besides, the full combination of your given name and surname is rarely ununique, although this can happen. Besides, the SIN is unique, with which I have updated my comment. =) – Will Marcouiller Jan 15 '15 at 15:34
  • With InnoDB, MySQL gives you a primary key whether define one or not, but I'm guessing this framework isn't specific to MySQL. – Marcus Adams Jan 15 '15 at 15:34
  • You can add the system timestamp as a primary key to your logging table to make it work. I would personally combine timestamp with another field (process or something similar) to create a compositie primary key that will have a high probability of being unique. You may have multiple log entry at the same time, but how many logging entries for differen processes will happen at the same time? – Michael Humelsine Jan 15 '15 at 15:43
  • @MichaelHumelsine I don't think that a lot of logging entries will happen at the same time. But that doesn't mean it wont happen. Maybe a composite key that consists of DateTime and the specific entity pk will work, like they mentioned [here](http://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key?lq=1) – Robin Gordijn Jan 15 '15 at 15:57
  • @RobinGordijn if you are working with entities in your domain that will work, but if you are working with processes or code outside application's domain that might not be possible. Maybe an easy example is a JobScheduler. JobName + TimeStamp is going to be unique (in the real word). If you are the corner case where it's not, using a singleton pattern on the logging class would allow you to serialize logging entries (to make TimeStamp unique) and eliminate the problem. Short answer, you need a primary key and have one...you just need to identify it. – Michael Humelsine Jan 15 '15 at 16:06

1 Answers1

4

why isn't it possible for EntityFramework to work with tables without primary keys?

That was a design decision. Entity Framework doesn't want to do updates when the uniqueness of a row cannot be determined.

They could have decided to support it, for example by adding where oldcolN=oldvalN for all columns, but that could still potentially update more rows than intended.

CodeCaster
  • 147,647
  • 23
  • 218
  • 272
  • I hadn't considered the question under that angle, interesting! =) – Will Marcouiller Jan 15 '15 at 15:30
  • Worst case is that the RDBMS engine looks at the entire row tuple to determine uniqueness. It wouldn't be that difficult for EF to do the same via GetHashCode or something similar. – Brian Driscoll Jan 15 '15 at 15:31
  • 3
    @BrianDriscoll - and when two rows have the same values in all columns, what then? – hatchet - done with SOverflow Jan 15 '15 at 15:33
  • @hatchet Metadata (for the engines that support it). I'm actually getting this all from a graduate level resource (textbook), lest you think I'm just babbling here. – Brian Driscoll Jan 15 '15 at 15:34
  • 1
    @Brian this feels like pulling an answer out of you. If you know more, please share. What metadata do you mean and how would that fit in Entity Framework's database-agnostic, disconnected model scenario? – CodeCaster Jan 15 '15 at 15:34
  • Off the top of my head, enabling CDC in a MSSQL db would add metadata such as createdate, lastupdateddate, etc. I don't know off-hand what if any other RDBMS engines have similar capabilities. I agree it's a stretch solution and certainly not my first choice, but it would work. – Brian Driscoll Jan 15 '15 at 15:41
  • @Brian I'm not sure that [Change Data Capture](http://msdn.microsoft.com/en-us/library/cc645937.aspx) (right?) allows to retroactively uniquely identify a row in a source table without PK for updates. Also, [MySQL doesn't have](http://stackoverflow.com/questions/2258052/) an internal identifier like Oracle's ROWID. MSSQL's [ROW_NUMBER](http://msdn.microsoft.com/en-us/library/ms186734.aspx) also won't work, as the row number may change if other records are C/U/D between loading and updating. I'm sure you're onto something, but don't see the concrete implementation yet, let alone cross-RDBMS. – CodeCaster Jan 15 '15 at 15:46
  • I agree, and honestly I was talking/thinking more on the RDBMS side than EF. My understanding as of sometime in 2013 was that it is possible to create a DefiningQuery in the SSDL to uniquely identify entities that have no primary key, but I guess that's no longer the case? – Brian Driscoll Jan 15 '15 at 15:48
  • I see, I didn't need to update my table. But I understand that a Framework would need this information also for adding new records. – Robin Gordijn Jan 15 '15 at 15:53