-2

I'm working on a online store. I keep the added items in the cookies and at the time of check out, I place the order in the database. In my DB I have two tables orders, orderItems. 

Order:
[Order_Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Customer_Id] INT --FK--
....

OrderItems
[Order_Id] INT --FK to Order--
[Item_Id] INT --FK to Item table--

When I place the order first in order table I need the Order_Id to be able to properly place items for the order. I know that SCOPE_IDENTITY would give me the last identity in the table but I think this might cause some concurrency problem. I'm using EF6 so I'm not sure even if I can use the SCOPE_IDENTITY but even though I can I'm still concerned about the concurrency problems. What I wanna know is how to get the auto-incremented identity right after the insert ?

I'm using EF 6 as ORM and the website is written in ASP.NET MVC C#.

JAX
  • 1,540
  • 3
  • 15
  • 32
  • 1
    Within a stored procedure one would use the OUTPUT clause as described here: http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value – Pieter Geerkens Sep 30 '14 at 00:20
  • What sort of concurrency problems do you think scope_identity has? I've used it quite a bit and do not find it wanting... – Ben Thul Sep 30 '14 at 01:58

1 Answers1

0

I think you can add a column in your order table and store the date and time of when the insertion takes place to milliseconds. Assign this value to a local variable before performing the insert and try to query that back. This solves your concurrency problem.

Transcendent
  • 5,598
  • 4
  • 24
  • 47