0

I have an old database that I am interfacing with using the Entity Framework. I can't alter the schema too much because it is being used by another (very old) application, which will crash if it is not allowed to insert its own value for the column that is supposed to be the PK.

The tables have no primary key constraints set, so no identity is available. They are set to be entity keys in the EF designer though (not code-first). Selections works great, but it fails on insert because it can't figure out what the value for the primary key should be. I tried setting the StoreGeneratedPattern to none, computed, and identity but none of them worked.

VS 2013, EF 6 I think (honestly not sure how to check because the ADO.net Entity Data Model project doesn't specify)

Zorgarath
  • 979
  • 12
  • 23
  • Well, you have to provide a PK value yourself. `DatabaseGeneratedOption` doesn't apply here, because the database doesn't generate anything. – Gert Arnold Feb 23 '17 at 21:53
  • 1
    Would any of the columns in this table be a suitable "fake" primary key? That is, it contains unique values? If so, you can trick EF into using that column as a primary key, and I can answer the question. –  Feb 23 '17 at 21:57
  • Have you considered using a stored proc for the insert instead of EF? – HLGEM Feb 23 '17 at 22:05
  • @Amy But even then a unique value for the real PK must be supplied. – Gert Arnold Feb 24 '17 at 09:41
  • Which EF version do you have? – Gert Arnold Feb 24 '17 at 09:42
  • @GertArnold there isn't a real primary key. "The tables have no primary keys set" –  Feb 24 '17 at 14:06
  • @Amy It's a bit ambiguous: "which will crash if it is not allowed to insert its own PK values." vs. "no primary keys set". I think OP should clarify. – Gert Arnold Feb 24 '17 at 14:09
  • @Amy I have "faked" primary keys for EF, I set the specific columns to be Entity Keys, and thus should be non-nullable and unique. Making EF guess what value should come next for the fake PK is the problem. – Zorgarath Feb 25 '17 at 09:32
  • @GertArnold I've edited the question: there is no PK constraint or indexing on the tables, but there are columns that behave like primary keys. I believe its EF 6 – Zorgarath Feb 25 '17 at 09:35
  • One way or the other you have to find "next available" values that uniquely identify a new entity. If you want help with that you should give more info on the db content and structure. Why don't you look at how the old application does it? – Gert Arnold Feb 25 '17 at 13:21
  • @GertArnold The old application simply ran a `MAX(ID)+1` query to get the next value for the key. Can this be done in EF? – Zorgarath Feb 26 '17 at 04:22
  • EF6 and lower don't have built-in functions for this. EF-core does, but (IMO) that's not production-ready yet. – Gert Arnold Feb 26 '17 at 15:53

1 Answers1

0

Not the coolest answer, but still would work: Set the ID to a GUID in your data access layer for new inserts. If you have to resort to fake PKs, then this still is at least consistent with PK philosophy despite worse performance than normal identity.

Here's some important tips, though they might not be super helpful if you can't make any changes to the db:

What are the best practices for using a GUID as a primary key, specifically regarding performance?

Sometimes you have to do bad things to make old things work. :-/

Michael Davidson
  • 515
  • 9
  • 15
  • I don't think this would work because the legacy software would get messed up seeing GUIDs in the pk column, or seeing null pk's if the GUID is inserted as an extra column. Thanks for the suggestion though! – Zorgarath Feb 25 '17 at 09:39