0

I have an ID that is set to auto increment (int obviously).

var dc = new DataContext([STRING]);

var usersTable = dc.GetTable<Audit_User>();

var user = usersTable.FirstOrDefault(o => o.Username.Equals("NAME"));

if (user == null)
{
   user = new Audit_User()
   {
      Username = "NAME"
   };

   usersTable.InsertOnSubmit(user);
}

//HERE - I need access to the user Id

dc.SubmitChanges();

For more context, please see the tags.

3 Answers3

4

//HERE - I need access to the user Id

Actually you do not. If you consistently use linq-2-sql you can assign the user entity instead of setting the user.id to populate a relation (most probable cause why you need the ID).

So instead of doing

somerelated_table.fk_user_id = user.id

you do

somerelated_table.user = user

Linq-2-sql will handle the proper assignment of the user.id when you call SubmitChanges. Plus as a bonus it will all be done in a single transaction. That is the beauty of linq-2-sql.

If you need the Id for some manipulation without using linq-2-sql, you can anyhow do it after the submitchanges and it will not matter anyway

Pleun
  • 8,856
  • 2
  • 30
  • 50
  • Thanks, changed this to the answer. Very helpful, and opens my eyes to the way linq-2-sql works too, thanks again. You were right - that is exactly why I "needed" the value of the user id. –  Oct 05 '12 at 07:01
  • 1
    Your welcome, it takes a bit of time to get used to L2S it but aferwards you will be more productive.. – Pleun Oct 05 '12 at 11:25
1

You can't get ID before submitting data to SQL server because it is generated on server side. If you badly need to do so, I suggest generating ID on client side (which may be painful).

Another question is why do you need to access that ID before inserting a row, in most cases it may be misunderstanding of how linq2sql works.

Sometimes there is no Foreign Key set up in the database for the tables. To insert the ID in the referenced table, just take it from yourEntity.ID right after insert, it should be filled by the system. Don't forget to submit changes again.

Maxim Daigle
  • 393
  • 3
  • 8
Anri
  • 6,175
  • 3
  • 37
  • 61
  • Ultimately, I aim to insert a record into another table that references this user, and so I need the ID from the user. –  Oct 04 '12 at 13:48
  • 2
    You will find it in user.Id after submit – Anri Oct 04 '12 at 13:56
  • It's worth noting that server-side generation of primary key values can be disabled on `Id` by turning off `IsDbGenerated` (*"Auto Generated Value"*) on the property in the L2S .dbml file. – Rob Oct 04 '12 at 13:58
  • 1
    -1: No, I am sorry but this is not the way linq-2-sql is designed. You are thinking too much database oriented. You have to assign entities, not foreign keys.. See my answer – Pleun Oct 04 '12 at 20:14
  • @Pleun That's true, i was answering direct question, your answer is better – Anri Oct 04 '12 at 22:14
0

Well, in your code, user.ID includes the user id (if it was existed before, and of course if it's a new one). I'm not sure about that, but i think that SubmitChanges also automatically query the new ID of new records and fill the existing object identity property automatically, so in your case using user.ID won't cost additional query if you use it after the SubmitChanges (it'll be 0 if it's new record and you didn't call SubmitChanges)

But, if you want to use the id before calling SubmitChanges, the best way to define the 'ID' before adding it to the database is making the ID a uniqueidentifier on the database (System.Guid), and then simply inserting it with the GUID already defined in the INSERT statement.

Just notice that the insert could fail, and in that case you must make sure that if something went wrong anything you did while assuming user with the same ID as the Guid you're using exists will be rolled back.

Shahar Gvirtz
  • 2,418
  • 1
  • 14
  • 17