LINQ to SQL Insert Serial GUID

Question

I have a database that is part of a merge replication scheme that has a GUID in the form of PK. In particular, the data type is a unique identifier, the default value (newsequentialid ()), the RowGUID parameter is set to Yes. When I do InsertOnSubmit (CaseNote), I thought I could leave CaseNoteID on my own and the database would enter the next Sequential GUID, as if you manually entered a new line in the MSSMS. Instead, it sends 00000000-0000-0000-0000-000000000000. If I add CaseNoteID = Guid.NewGuid(), I will get a GUID, but not a serial one (I'm sure).

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

Below is the code that I use 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 sentences below, I turned Autogenerated into LINQ for this column, and now I get the following error:> The target table of the DML statement cannot have triggers activated if the statement contains an OUTPUT clause without an INTO clause. Ideas?

Answer 1

In the Linq to Sql constructor, set the Auto Generated Value property to true for this column.

This is equivalent to the IsDbGenerated property for the column. The only limitation is that you cannot update the value using Linq.

Answer 2

At the top of the Linked window on the right:

Serial GUID in Linq-to-Sql?

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

Answer 3

Regarding your "DML statement target table, triggers cannot be involved if the statement contains an OUTPUT clause without an INTO clause", check this MS KB article, it looks like an error in LINQ:

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

Answer 4

You really needed to do a couple of things.

• Remove any property assignment of type GUID
• Change the column to auto-generated
• Create a default database constraint for the column: NEWSEQUENTIALID ()
• Paste paste as before.

When inserted into the table, an identifier will be created and will be sequential. Comparison of the performance of the NEWSEQUENTIALID () method and other methods

Answer 5

An error appears in Linq2Sql when using the automatically generated primary key (guid / sequential guid) and the presence of a trigger on the table .. this is what causes your error. There is a fix for the problem:

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

Answer 6

Masstransit uses combguid:

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

Is this what you are looking for?

from wikipedia:

Sequential algorithms

GUIDs are usually used as the primary key for database tables, and with this often the table has a clustered index for this attribute. This leads to performance problems when inserting records, since a completely random GUID means that the record may need to be inserted anywhere in the table, and not just attached near the end. As a way to mitigate this problem while ensuring sufficient randomness to effectively prevent duplicate collisions, several algorithms have been used to generate consecutive GUIDs. The first method, described by Jimmy Nilsson in August 2002 [7] and referred to as “COMB” (“combined guid / timestamp”) replaces the last 6 bytes of Data4 with the least significant 6 bytes of the current system date / time. Although this can lead to GUIDs that are generated out of order for the same fraction of a second, his tests have shown that this has little real-world impact on implementation. One of the side effects of this approach is that the date and time of insertion can be easily extracted from later, if desired. Starting with Microsoft SQL Server 2005, Microsoft has added a function for the Transact-SQL language called NEWSEQUENTIALID (), [8] that generates GUIDs that are guaranteed to increase in value, but can start with a smaller number (still guaranteed to be unique) when the server restarts . This reduces the number of database tables where inserts may occur, but does not guarantee that values ​​will always increase. The values ​​returned by this function can be easily predicted, so this algorithm is not suitable for creating obscure numbers for security or hash purposes. In 2006, a programmer discovered that the SYS_GUID function provided by Oracle returned sequential GUIDs on some platforms, but this seems to be a bug, not a function. [nine]

Answer 7

You must process the OnCreated () method

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