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?

Keltex · Accepted Answer · 2009-04-15 18:23:42Z

5

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.

edited Apr 15, 2009 at 18:23

answered Apr 15, 2009 at 18:17

Keltex

26.5k12 gold badges82 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Refracted Paladin Over a year ago

I tried this and I now 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.

DCShannon Over a year ago

For me this was already set, but the DB still tries to assign the default 0's guid rather than generating one.

Community · Accepted Answer · 2017-05-23 12:13:35Z

5

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

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.

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Apr 15, 2009 at 18:17

Michael Meadows

28.5k4 gold badges50 silver badges63 bronze badges

4 Comments

Adam Robinson Over a year ago

+1. If you want order, use a numeric type. Guids were designed to function as unique identifiers, not provide any method of ordering.

Michael Meadows Over a year ago

true, but it can cause a lot of churn in two cases: clustered indexing and replication performance (which looks like the OP's problem). COMB helps with that, but doesn't necessarily make them sequential.

Refracted Paladin Over a year ago

@Michael - thanks for pointing that out. It is strange that it wasn't one of the "suggestions" when I created the Title

Michael Meadows Over a year ago

saved me from having to find the article that I originally found COMB GUIDs on many years back.

Ashley Leonard · Accepted Answer · 2009-07-31 20:46:41Z

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

C Tierney · Accepted Answer · 2010-07-15 12:47:33Z

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

@C Tierney: How does this differ from the accepted solution? Sounds like the same thing, just spelled out, correct?

Nicholas Head · Accepted Answer · 2010-02-05 19:52:41Z

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

user156888 · Accepted Answer · 2012-05-25 09:38:16Z

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]

RonaldPaguay · Accepted Answer · 2016-04-13 20:48:32Z

You must handle OnCreated() method

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

Collectives™ on Stack Overflow

LINQ to SQL Insert Sequential GUID

7 Answers 7

2 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Comments

Hot Network Questions