Linq to SQL - How to find the value of the IDENTITY column after InsertOnSubmit()

sql linq linq-to-sql identity-column
Ash Machine picture Ash Machine · Apr 30, 2009 · Viewed 18.6k times · Source

I am using LINQ to SQL to insert simple data into a table WITHOUT a stored procedure. The table has a Primary Key ID column, which is set as an IDENTITY column in both SQL Server and in my DBML.

First I call InsertOnSubmit(); with data for a single row, and then I call SubmitChanges(); to commit the row to the db. But I think there must be a simple way to then retrieve the row's IDENTITY column value of the newly inserted row. I don't wish to provide the IDENTITY column value to the db, I want it to generate one for me.

How is this best handled?

Answer

Vikram picture Vikram · Apr 30, 2009

here is a simple code snippet

            Dim odb As New DataClassesDataContext
            Dim tst As New test
            tst.name = "abcd"
            odb.tests.InsertOnSubmit(tst)
            odb.SubmitChanges()
            Response.Write("id:" + tst.id.ToString)

so, basically the object you used in InsertOnSubmit will get the identity field populated after the record has been created in the database

Related questions

How to store a list in a column of a database table

So, per Mehrdad's answer to a related question, I get it that a "proper" database table column doesn't store a list. Rather, you should create another table that effectively holds the elements of said list and then link to it …

Read More
LINQ to SQL - Left Outer Join with multiple join conditions

I have the following SQL, which I am trying to translate to LINQ: SELECT f.value FROM period as p LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17 WHERE p.companyid = 100 I have seen …

Read More
How can you handle an IN sub-query with LINQ to SQL?

I'm a bit stuck on this. Basically I want to do something like the following SQL query in LINQ to SQL: SELECT f.* FROM Foo f WHERE f.FooId IN ( SELECT fb.FooId FROM FooBar fb WHERE fb.BarId = 1000 ) Any …

Read More