Does LINQ To SQL auto update the LOCAL/CLIENT id column after a SubmitChanges call?

Question

just want to know if linq to sql auto updated the id column of a class (table row object) after SubmitChanges is called inserting a new row to that table, that would be fantastic, would anyone be able to confirm this?

Answer 1

Yes, it does as long as the AutoSync and IsDbGenerated of ColumnAttribute are set to AutoSync.OnInsert and true respectively.

Thus:

[Column(
     Storage="_Id",
     AutoSync=AutoSync.OnInsert,
     DbType="Int NOT NULL IDENTITY",
     IsPrimaryKey=true,
     IsDbGenerated=true
)]

These settings are accessible in the designer. AutoSync.OnInsert and true are the default settings.

Here you can see this in action:

var db = new MessageDataContext();
db.Log = Console.Out;
Message m = new Message();
m.Text = "Hello, world!";
db.Messages.InsertOnSubmit(m);
db.SubmitChanges();

Here the table Message has two columns Id (an autonumber PK column) and Text. This causes the following to be printed on the console:

INSERT INTO [dbo].[Message]([Text])
VALUES (@p0)

SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]
-- @p0: Input VarChar (Size = 13; Prec = 0; Scale = 0) [Hello, world!]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

The second SQL statement shows the DataContext automatically retrieving the identity assigned to the Message that we inserted into the database.

Thus:

db.Messages.InsertOnSubmit(m);
db.SubmitChanges();
Console.WriteLine(m.Id);

will print the Id assigned to m.

Answer 2

You can verify that an IDENTITY column will be updated on SubmitChanges() by looking at the [Column] attribute for the mapped property (or <Column> element if you are using an external XML mapping file).

There are two properties that will be set on the [Column] attribute, IsDbGenerated = true, and AutoSync = OnInsert - the first attribute tells LINQ that the target column's value is created by the database such as for IDENTITY columns or TIMESTAMP / ROWVERSION columns, and the latter tells LINQ to update the model object with the value after a database insert.

Both properties need to be set like this for the expected behaviour to occur. If you use SqlMetal or the Visual Studio designer, the generated code will normally handle this for you automatically, as long as the column was IDENTITY or TIMESTAMP to begin with. If you change the column to an IDENTITY type later on, you will either need to regenerate the LINQ code, or update the attributes manually yourself.