How can I prevent Linq from adding GUIDs so that SQL Server can do this?

Question

I create and populate LINQ to SQL objects programmatically, and then add them to the sql server database with a unique column for the PC. LINQ automatically assigns PK all 0s. This creates insertion errors because 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 PK using myNewRecord.pk = Guid.newGuid () Guid is it all 0 (zeros)?
• But I really want the SQL server to assign GUIDs, because I assume it has some method to make sure it does not redirect the same guid

Is there a way to tell LINQ that I don't want to populate the GUID myself, but leave it on the SQL server?

Answer 1

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

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

EDIT: I only realized that you are using Linq for sql, not Linq for objects. However, I'm sure there is something similar in L2S to handle this.

Edit2: There is a similar thing in linq for sql as shown in the screenshot. Changing this property resolves the issue.

Answer 2

You can annotate your GUID column to indicate that it was created using IsDbGenerated :

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

Answer 3

I assume that it has some method to make sure that it is not reassigning the same guid

The whole point of Guides is that they are unique ... or, at least, usually unique. If they are all assigned from the same source, for example, calling Guid.NewGuid() on your server, this is a fairly safe bet that you won, encountered any collisions.

Answer 4

The problem is that since your PK has a value type, it always has some value (no null equivalent). Therefore, if the generated classes do not include certain functions for tracking, if you manually set the value and omit it, if you have no, there is no way to do this.

Therefore, source searches for IceCreamTrackerTable will provide a definitive answer.