5

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). I want to do this in LINQ To SQL and not in Raw SQL (I no longer use Raw SQL).

Also, second part of my question is: I am always careful to know the ID of a user that's 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 (?) 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 difference worth always keeping the userid (int32) in say, session state?

@RedFilter provided some interesting/promising leads for the first question, because I am at this stage unable to try them, if anyone knows or can confirm these changes that he recommended in the comments section of his answer?

dty
  • 18,795
  • 6
  • 56
  • 82
Erx_VB.NExT.Coder
  • 4,838
  • 10
  • 56
  • 92

5 Answers5

12

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.

calico-cat
  • 1,324
  • 6
  • 20
  • 36
  • @calico: thanks mate that was explained perfectly, also refreshing to see some vb code up here every now and then, given u upvote and accept answer so now you can start downvoting peeps :) – Erx_VB.NExT.Coder Jan 21 '10 at 03:26
  • just to let you know, i tried to call the PorductID of a referenced object after submitting and it did not work at all. though i haven't tried ur above code "as is" but it was the same in principle, any idea why it may be picky? – Erx_VB.NExT.Coder Feb 23 '11 at 05:17
  • Are you sure the submission was successful? Are you sure you're still referencing the same object? I don't know your code so I can't be sure. – calico-cat Feb 24 '11 at 10:06
  • I don't think it works out of the box, i think if we implement the changes that RedFilter made reference to in the comments section of his answer, then it should probably work? (Yet to try this). – Erx_VB.NExT.Coder Apr 06 '12 at 12:43
4

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.

David Hall
  • 32,624
  • 10
  • 90
  • 127
  • hello, thanks, your right its auto incremented during SubmitChange, thats not a problem - i need to keep this number/id to use it to call other records really, thats the only reason why i want it in session state. – Erx_VB.NExT.Coder Jan 18 '10 at 04:21
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.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • hi orbMan, if linq to sql auto updated the id column of a class after a SubmitChanges, that would be fantastic, would anyone be able to confirm this? – Erx_VB.NExT.Coder Jan 18 '10 at 04:22
  • @Erx_VB.NExT.Coder: It will update the id column on inserts, if you tell it to. By default, if your id column is of type int, it will automatically be set to "generated on insert", if it is of type guid, then you have to change the default to "generate on insert". You do that from the Linq 2 SQL designer. – Egil Hansen Jan 18 '10 at 08:57
  • @EgilHansen ah, fantastic, because when i tried this, it didnt work out of the box, i will implement these changes and try again and let you know how it goes (as soon as i get back on this particular project or another that uses LINQ To SQL). – Erx_VB.NExT.Coder Apr 06 '12 at 12:40
0

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.

Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • i dont know how to write sql procedures in sql lang. but if i could im pretty sure linq can map to sql stored procedures in it's .dbml file designer, so it should be able to get return values, probably only if you do the entire INSERT using t-sql... otherwise i am unsure as well. – Erx_VB.NExT.Coder Jan 18 '10 at 03:41
  • i dont think InsertOnSubmit or Submit as a return value thats relevant to our needs. – Erx_VB.NExT.Coder Jan 18 '10 at 03:42
0

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

RickNZ
  • 18,448
  • 3
  • 51
  • 66