scope_identity() and the identity of inserted records
When inserting a record in a normal table, which has an auto-number ID column, you'd expect to be able to do this;
And for that to show the identity of the just-inserted record. However, if you insert into an l-view, scope_identity() returns null.
I'm trying to adapt Anchor Modeling to work with Entity Framework, and EF is generating some code like that above. It uses that so it can track the ID of the element you just added.
Is there a good way to get the ID of the recently-inserted element? I think a select statement like "(select top(1) RO_ID from lRO_Role order by RO_ID) desc" would not necessarily be thread-safe -- that is, if I'm inserting at the same moment as someone else, I don't know that I'd get my ID back and not theirs.