LINQ to SQL Insert Sequential GUID

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?

Upvotes: 7

Answers (7)

RonaldPaguay

Reputation: 335

You must handle OnCreated() method

Partial Class CaseNote
    Sub OnCreated()
        id = Guid.NewGuid()
    End Sub
End Class

Upvotes: 0

user156888

Reputation:

Masstransit uses a combguid :

https://github.com/MassTransit/MassTransit/blob/master/src/MassTransit/NewId/NewId.cs

is this what you're looking for?

from wikipedia:

Sequential algorithms

GUIDs are commonly used as the primary key of database tables, and with that, often the table has a clustered index on that attribute. This presents a performance issue when inserting records because a fully random GUID means the record may need to be inserted anywhere within the table rather than merely appended near the end of it. As a way of mitigating this issue while still providing enough randomness to effectively prevent duplicate number collisions, several algorithms have been used to generate sequential GUIDs. The first technique, described by Jimmy Nilsson in August 2002[7] and referred to as a "COMB" ("combined guid/timestamp"), replaces the last 6 bytes of Data4 with the least-significant 6 bytes of the current system date/time. While this can result in GUIDs that are generated out of order within the same fraction of a second, his tests showed this had little real-world impact on insertion. One side effect of this approach is that the date and time of insertion can be easily extracted from the value later, if desired. Starting with Microsoft SQL Server version 2005, Microsoft added a function to the Transact-SQL language called NEWSEQUENTIALID(),[8] which generates GUIDs that are guaranteed to increase in value, but may start with a lower number (still guaranteed unique) when the server restarts. This reduces the number of database table pages where insertions can occur, but does not guarantee that the values will always increase in value. The values returned by this function can be easily predicted, so this algorithm is not well-suited for generating obscure numbers for security or hashing purposes. In 2006, a programmer found that the SYS_GUID function provided by Oracle was returning sequential GUIDs on some platforms, but this appears to be a bug rather than a feature.[9]

Upvotes: 0

C Tierney

Reputation: 1121

You really needed to do a couple of things.

Remove any assignment to the GUID type property
Change the column to autogenerated
Create a constraint in the database to default the column to NEWSEQUENTIALID()
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

Upvotes: 1

Nicholas Head

Reputation: 3726

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

Upvotes: 0

Ashley Leonard

Reputation:

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

Upvotes: 1