This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product. Otherwise - post away!
How do I get the identity value of a newly added record?
And how about if I'm add()ing 10 records and want to get the identity for each of them?
Or maybe I'm trying to do it the hard way.... The scenario is that I'm importing a bunch of "persons" from Excel, each of which is assigned to a number of regions. So in my model I have a through association between Person and Region.
My plan is to insert each person, then step through the Excel "region" columns for that person and insert the appropriate ones into PersonRegion using the newly created person's identity value and whatever the region id is for each region they're assigned to. Or is there some magical way I can do:
person.personregion.regionid = xx;
.......... or something like that?
[quote user="Danzig"]How do I get the identity value of a newly added record?[/quote]
From the Id property.
In most cases, LightSpeed allocates an ID and sets the Id property as soon as you add the entity to the unit of work. So you can use the Id property any time after calling Add.
The exception is if you are using the IdentityColumn identity method. In this case, the database allocates the Id, so you can only use the Id property after calling SaveChanges. But LightSpeed does retrieve the ID allocated by the database and set the Id property accordingly.
Regarding your specific case, you shouldn't actually need to worry about IDs, you can just use entities directly. So if you have a through association between Person and Region, you can go:
It doesn't matter if somePerson and/or someRegion haven't had IDs allocated for them yet: LightSpeed will figure that stuff out for you. Nor do you need to create and add PersonRegion objects (join objects) explicitly: LightSpeed will create and add those objects as you add entries to the through association. (And conversely will remove them if you remove entries from the through association.)
(I will warn you that there is a known bug if you add regions to a new Person and then remove them again before saving, but this shouldn't be an issue in your import scenario.)
[quote user="ivan"]In most cases, LightSpeed allocates an ID and sets the Id property as soon as you add the entity to the unit of work. So you can use the Id property any time after calling Add.
Ups... After reading this I realised we have a serious potential pitfall in our application with LS.
The problem is when we will use our app on projects with SQL Server (which is practically guranteed), all this code will become invalid and start generating errors.
Is there a way to make LS behave the same independent of DB/identity strategy (I understand it's also a question of is it possible to get ID of Identity column prior to insert)?
It *should* still work. If you are using IdentityColumn, LightSpeed will allocate a temporary ID to each entity as it's added to the UOW. You can use that temporary ID to set foreign keys. When you call SaveChanges, LightSpeed fixes up each temporary ID to be the value returned from the database, and fixes up all FKs that refer to that temporary ID as well. So that when the entity with the FK comes to be saved, its FK has been fixed up to contain the real database ID.
That said, we generally advise against IdentityColumn in a greenfield database design anyway. KeyTable is more efficient (IdentityColumn defeats batching of inserts) and means you get your IDs allocated up front. You will find KeyTable behaves MUCH more like the Oracle sequence strategy.