LINQ to SQL Insert Sequential GUID

Question

I have a database that is part of a Merge Replication scheme that has a GUID as it's PK. Specifically the Data Type is uniqueidentifier, Default Value (newsequentialid()), RowGUID is set to Yes. When I do a InsertOnSubmit(CaseNote) I thought I would be able to leave CaseNoteID alone and the database would input the next Sequential GUID like it does if you manually enter a new row in MSSMS. Instead it sends 00000000-0000-0000-0000-000000000000. If I add CaseNoteID = Guid.NewGuid(), the I get a GUID but not a Sequential one (I'm pretty sure).

Is there a way to let SQL create the next sequential id on a LINQ InsertOnSubmit()?

For reference below is the code I am using to insert a new record into the database.

            CaseNote caseNote = new CaseNote
                                {
                                    CaseNoteID = Guid.NewGuid(),
                                    TimeSpentUnits = Convert.ToDecimal(tbxTimeSpentUnits.Text),
                                    IsCaseLog = chkIsCaseLog.Checked,
                                    ContactDate = Convert.ToDateTime(datContactDate.Text),
                                    ContactDetails = memContactDetails.Text
                                };
        caseNotesDB.CaseNotes.InsertOnSubmit(caseNote);

        caseNotesDB.SubmitChanges();

---

Based on one of the suggestions below I enabled the Autogenerated in LINQ for that column and now I get the following error --> The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. Ideas?

Answer 1

In the Linq to Sql designer, set the Auto Generated Value property to true for that column.

This is equivalent to the IsDbGenerated property for a column. The only limitation is that you can't update the value using Linq.

Answer 2

From the top of the "Related" box on the right:

http://stackoverflow.com/questions/665417/sequential-guid-in-linq-to-sql

If you really want the "next" value, use an int64 instead of GUID. COMB guid will ensure that the GUIDs are ordered.

Answer 3

If you're looking for sequential numbers, you don't want guids. If you need them to be in a certain order (the order you're adding them), then you don't want guids.

A guid is nice because it is guaranteed (for all intents and purposes) to be globally unique, based on the way it is generated. If you're going around that method of generation to create sequential values, you're not really creating guids...you're creating something that looks like a guid...but doesn't really have any of the benefits. And it could potentially cause trouble later, if someone assumes it's a guid and is guaranteed to be unique, when in fact, it's been duplicated elsewhere in the database because someone has restarted a process or re-imported some data, etc.

Answer 4

In regards to your "The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause", check out this MS KB article, it appears to be a bug in LINQ:

http://support.microsoft.com/kb/961073

Answer 5

There is a bug in Linq2Sql when using an auto-generated (guid/sequential guid) primary key and having a trigger on the table.. that is what is causing your error. There is a hotfix for the problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;961073&sd=rss&spid=2855

Answer 6

You really needed to do a couple of things.

1. Remove any assignment to the GUID type property
2. Change the column to autogenerated
3. Create a constraint in the database to default the column to NEWSEQUENTIALID()
4. Do insert on submit just like you were before.

On the insert into the table the ID will be created and will be sequential. Performance comparison of NEWSEQUENTIALID() vs. other methods