1

Similar to This Question using linq to SQL, but I don't want to just execute SQL commands from the code. I could write a stored procedure.

I am writing the year rollover functions for an application and I would like to be able to make sure that the next year uses the next available PK slot so that I can use math to go back between years.

The user wants a roll back function also, so there is the distinct possibility of gaps since a year will be deleted at that point.

This also begs the question of whether relying on pk values to be sequential is too brittle...

Question: Is there a way to short-circuit the way EF inserts records and specify the primary key I would like inserted with the record?

Community
  • 1
  • 1
Noel
  • 600
  • 16
  • 37
  • 2
    This isn't a direct answer to your question, but I agree with Paul that relying autoincrement PK values to be sequential *is* too brittle. However, if your table is going to contain exactly one row per year (which it sounds like it is since you're talking about doing math), then why not just *use the year as the primary key?* Seems that would avoid the brittle design problem and the IDENTITY INSERT problem in one go. – Aaron Mar 11 '11 at 04:22
  • That's definitely a solution, though there are long-running debates on the pro's and con's of natural keys: http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=SQL+using+natural+keys be sure you're up to speed on those before making that choice. – Paul Mar 11 '11 at 12:12
  • @Aaron: I'm not sure I have a choice as to whether the pk of the year lookup is auto-incremented, but trying to force a questionable design through the framework seems to brittle so I will not rely on the PK for math. – Noel Mar 11 '11 at 17:00

1 Answers1

1

I would say your design is absolutely too brittle. The PK really should not be an application concern except for retrieving a given record, imo.

That said, if you must do it that way, you can set the StoreGeneratedPattern flag to "None" and then insert whatever PK you want to from the app, but of course if hte DB itself is using an autoincrementing key of some kind (e.g. IDENTITY), then you'll still break.

Update Why do the requirements to a) have one row per year and b) roll back each year translate into anything at all for the PK? Why not just have a 'year' column (set to UNIQUE or not) which can be used in your query?

Paul
  • 35,689
  • 11
  • 93
  • 122
  • The Year that I'm adding is part of a lookup table. That year id is part of quite a few tables and is used for calculating the amount of money an organization will get for next year based on the data they provide during the current year. – Noel Mar 11 '11 at 16:57