How do i prevent Linq from adding a GUID so that SQL server can do it?

Question

I am creating and filling LINQ to SQL objects programatically and then adding them to a sql server database with a uniqueidentifier column for a PK. LINQ is automatically assigning a PK of all 0s. This creates errors upon insert because the PK is a duplicate.

Dim myNewRecord as IceCreamTrackerTable
myNewRecord.name=Bob
myNewRecord.favoriteIceCream=Vanilla
   'myNewRecord.PK=00000000-0000-0000-0000-000000000000 (by default)'
myDataContext.IceCreamTrackerTables.insertOnSubmit(myNewRecord)
    ''second record added throws an error because it also has PK 00000 etc.

I know that I can assign the PK using myNewRecord.pk=Guid.newGuid() Guid is all 0's (zeros)?
But what I really want is for SQL server to be assigning the GUIDs because I'm assuming that it has some method to make sure that it is not re-assigning the same guid

Is there any way to tell LINQ that I don't want to fill the GUID myself but leave it up to SQL server?

score 3 · Answer 1 · answered Mar 16 '12 at 17:43

You can annotate your GUID column to indicate that it is DB generated using IsDbGenerated :

[Column(Storage="_PK", 
        AutoSync=AutoSync.OnInsert, 
        DbType="UniqueIdentifier NOT NULL", 
        IsPrimaryKey=true, IsDbGenerated=true)]
public Guid PK
{
  //...
}

Erik Funkenbusch · Accepted Answer · 2015-05-14T19:08:06.253

Guid's are value types, thus they cannot be null. That means when a Guid is created, it has to assign a default value (which is all 0's). SQL Server does not automatically generate GUID's for primary keys like it does for Identity columns. You can set the default value to NEWID(), but by default EF will ignore that and try to insert a value. You can override this by doing something like described here

http://softmindit.blogspot.com/p/using-guid-as-entitykey-in-entity.html

EDIT: I just realized you're using Linq to sql not Linq to entities. However, i'm sure there's something similar in L2S to deal with this.

Edit2: There is a similar thing in linq to sql, as shown in the screenshot. Changing this property fixes the problem

enter image description here

score 0 · Answer 3 · answered Mar 16 '12 at 17:37

0

I'm assuming that it has some method to make sure that it is not re-assigning the same guid

The whole point of Guids is that they're unique... or at least usually unique. If they are all being assigned from the same source, like calling Guid.NewGuid() on your server, it’s a pretty safe bet you won’t encounter any collisions.

answered Mar 16 '12 at 17:37

Bob Kaufman

12,864
16
78
107

Thanks. My worry is the GUIDs on SQL server and the GUIDs in Visual Studio aren't 'talking.' So there are two independent guid makers that each assign guids. I would rather have just one entity in charge of making the guids. Does Visual studio's Guid making method take into account the guids that are already in the DB? This seems unlikely to me. Am I missing something? – bernie2436 Mar 16 '12 at 17:40
No, it doesn't take it into account, but it doesn't have to. It is theoretically impossible to get duplicate GUID's no matter who is generating, no matter how fast, no matter what platform or software. – E.J. Brennan Mar 16 '12 at 17:44
@akh2103 - any Guids that are generated on a particular machine ultimately resolve to calling the same underlying method, whether it's called from a Visual Studio app, a function call within SQL server or a third-party application. I don't know the specifics of what gets evaluated, but I suspect it the current timer and the MAC address figure into it. Again, usually unique, but not 100.00000% guaranteed to be so. But if they're all being generated from the same source, it's extremely unlikely you'll encounter a collision. – Bob Kaufman Mar 16 '12 at 17:45
The GUID generator is not going to verify that a GUID value doesn't already exist in your table. How would it know where it's going to go? The GUID generator in SQL isn't going to do this verification either, and probably calls the same generator in the OS. The short story is: Don't let this worry you, the chances of ever generating the same GUID twice is infinitesimally small. – That Chuck Guy Mar 16 '12 at 17:46

score 0 · Answer 4 · answered Mar 16 '12 at 17:41

0

The problem here is that since your PK is of a value type, it always has some value (there is no equivalent of null). So unless the generated classes include specific functionality to track if you have "manually" set the value and omit it in case you have not then no, there is no way to do that.

Therefore, looking at the source for IceCreamTrackerTable would provide a definitive answer.

answered Mar 16 '12 at 17:41

Jon

428,835
81
738
806

Argh! I removed the PK from the LINQ to sql IceCreamTrackerTable object, and now get this error when trying to submit: Can't perform Create, Update, or Delete operations on 'Table(IceCreamTrackerTable)' because it has no primary key. – bernie2436 Mar 16 '12 at 17:42

How do i prevent Linq from adding a GUID so that SQL server can do it?

4 Answers4

Linked