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.

Answer 1

hey Kevin, quick question, might be dumb, but just to get a better idea...

are any of the properties you're setting on your new riskchange object the Primary Key property?

Also, what else do you do, before calling SubmitChanges()?

cheers

EDIT:

thanks Kevin. Whats going on with this line here?:

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

what kind of constraint have you got on the PK...? Why does it not look like this:

[IDENTITY] [int] IDENTITY(1,1) NOT NULL

?

EDIT Hey Kevin. I think the problem is you haven't set your PK to be the Identity Specifier, and so, its not auto incrementing the PK.

To fix the problem, either re-create the table by changing this line:

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

to this:

[IDENTITY] [int] IDENTITY(1,1) NOT NULL

or, if your using Mangement Studio, just go into the design of that table. Go to the properties of that field, and in the properties window (which usually appears below) set "Idenity Specification" (Is Identity) to YES.

Don't forget to update your LINQ 2 SQL DBML also

Answer 2

What is your primary key? And do you have any other keys/constraints on the table? It is probably one of those that is causing the problem. I suggest widening the search for matching records, or changing your constraints.

Answer 3

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 4

Hi Kevin,

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.

Answer 5

thanks guys i get the same message