InsertOnSubmit = Cannot add an entity with a key that is already in use

Question

I am trying to insert a record into a table using Linq but get the dreaded Cannot add an entity with a key that is already in use error

'If the same data exists for the same patient in a record less that 21 days old then drop it
Dim RecordLookup As Integer = 0
RecordLookup = (From rc In CDEvodb.RISKCHANGEs _
    Where rc.NHI = tmpNHI And _
    rc.RECDATE > Date.Now.AddDays(-21) And _
    rc.BPSYS = Convert.ToDecimal(Drow.Item("BPSYS")) And _
    rc.CHOL = Convert.ToDecimal(Drow.Item("CHOL")) And _
    rc.HDL = Convert.ToDecimal(Drow.Item("HDL"))).Count()
If (RecordLookup = 0) Then
Dim riskchange As New RISKCHANGE
    riskchange.NHI = Drow.Item("NHI")
    riskchange.RECDATE = Date.Now.Date()
    riskchange.RISK = CalculatedRisk
    riskchange.BPSYS = Drow.Item("BPSYS")
    riskchange.CHOL = Drow.Item("CHOL")
    riskchange.HDL = Drow.Item("HDL")

    Try
        CDEvodb.RISKCHANGEs.InsertOnSubmit(riskchange)
    Catch ex As Exception
        myLogging.OutputError("<" & DateTime.Now.ToString & "> " & "Error - creating risk change record in dataset for patient " & Drow.Item("NHI").ToString() & " - " & ex.Message)
    End Try

End If

Basically I lookup on the table for a matching record (not including the Identity field) that is less than 21 days old. If I don't find one I create an instance of a row and set to insert it.

The SubmitChanges function is call a few lines down.

Drow is a DataRow from a Dataset previously populated using an SQLClient connection (the reason being I have not full converted over to Linq yet, just doing new functionality for now).

Cheers in advance.

---

this is the create script for the table:

USE [CDEvolution]

GO

/****** Object: Table [dbo].[RISKCHANGES] Script Date: 05/13/2009 14:40:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[RISKCHANGES](

[NHI] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[RECDATE] [datetime] NOT NULL,

[RISK] [numeric](15, 0) NOT NULL,

[BPSYS] [numeric](15, 0) NOT NULL,

[CHOL] [numeric](15, 1) NOT NULL,

[HDL] [numeric](15, 1) NOT NULL,

[POSTED] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IDENTITY] [uniqueidentifier] NOT NULL CONSTRAINT [DF_RISKCHANGES_IDENTITY]  

DEFAULT (newid()),

CONSTRAINT [PK_RISKCHANGES] PRIMARY KEY CLUSTERED

(

[IDENTITY] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Answer 1

If you update your DBML, add this (IsDbGenerated="true") to the PK member:

    <Column Name="[IDENTITY]" Member="IDENTITY" Type="System.Guid" 
            DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" 
            CanBeNull="false" IsDbGenerated="true"/>

It will let SQL assign the default value (newid()).

Otherwise it will send the uninitilized guid ('00000000-0000-0000-0000-000000000000') which is what is essentially causing the problem as far as I can tell.

Your other option could be to extend the partial class and initialize the GUID in the OnCreated() event (which doesn't use the columns' newid() default, but does solve the problem:

    partial class RISKCHANGE
    {
        partial void OnCreated()
        {
            _IDENTITY = Guid.NewGuid();
        }
    }

Note that for existing entities (populated by a query) this value will be overwritten with the correct value when OnLoaded() has been processed.