LINQ To SQL: Getting primary key id number from Table WITHOUT making a second trip?

Question

hello, how would i get the primary key id number from a Table WITHOUT making a second trip to the database in LINQ To SQL?

right now, i submit the data to a table, and make another trip to figure out what id was assigned to the new field (in an auto increment id field).

also, seond part of my question is: i am always careful to know the id of a user thats online because i'd rather call their information in various tables using their id as opposed to using a guid or a username, which are all long strings... i do this because i think that SQL Server doing a numeric compare is much (tentative use of the word 'much') more efficient than doing a username (string) or even a guid (very long string) compare... my questions is, am i more concerned than i should be? is the pref. difference worth always keeping the userid (int32) in say, session state?

Answer 1

In T-SQL, you could use the OUTPUT clause, saying:

INSERT table (columns...)
OUTPUT inserted.ID
SELECT columns...

So if you can configure LINQ to use that construct for doing inserts, then you can probably get it back easily. But whether LINQ can get a value back from an insert, I'll let someone else answer that.

Answer 2

Linq to SQL automatically sets the identity value of your class with the ID generated when you insert a new record. Just access the property. I don't know if it uses a separate query for this or not, having never used it, but it is not unusual for ORMs to require another query to get back the last inserted ID.

Two ways you can do this independent of Linq To SQL (that may work with it):

1) If you are using SQL Server 2005 or higher, you can use the OUTPUT clause:

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table or table variable.

2) Alternately, you can construct a batch INSERT statement like this:

insert into MyTable
(field1)
values
('xxx');
select scope_identity();

which works at least as far back as SQL Server 2000.

Answer 3

Unless you are doing something out of the ordinary, you should not need to do anything extra to retrieve the primary key that is generated.

When you call SubmitChanges on your Linq-to-SQL datacontext, it automatically updates the primary key values for your objects.

Regarding your second question - there may be a small performance improvement by doing a scan on a numeric field as opposed to something like varchar() but you will see much better performance either way by ensuring that you have the correct columns in your database indexed. And, with SQL Server if you create a primary key using an identity column, it will by default have a clustered index over it.

Answer 4

If you have a reference to the object, you can just use that reference and call the primary key after you call db.SubmitChanges(). The LINQ object will automatically update its (Identifier) primary key field to reflect the new one assigned to it via SQL Server.

Example (vb.net):

  Dim db As New NorthwindDataContext
  Dim prod As New Product
  prod.ProductName = "cheese!"
  db.Products.InsertOnSubmit(prod)
  db.SubmitChanges()
  MessageBox.Show(prod.ProductID)

You could probably include the above code in a function and return the ProductID (or equivalent primary key) and use it somewhere else.

EDIT: If you are not doing atomic updates, you could add each new product to a separate Collection and iterate through it after you call SubmitChanges. I wish LINQ provided a 'database sneak peek' like a dataset would.

Answer 5

Calling a stored procedure from LINQ that returns the ID as an output parameter is probably the easiest approach.