InsertOnSubmit = Unable to add an object with an existing key

Question

I'm trying to insert a record into a table using Linq, but get scary I can not add an object with an already used key 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 

I basically look at the table for the corresponding record (not including the Identity field), which is less than 21 days. If I do not find it, I instantiate the string and install it.

The SubmitChanges function calls a few lines down.

Drow is a DataRow from a dataset that was previously populated using the SQLClient connection (the reason I have not fully converted to Linq yet, but is now just performing new functions).

Greetings in advance.

---

this is creating a 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 KEYBOARD

(

 [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"/> 

This will allow SQL to assign a default value (newid ()).

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

Another option would be to extend the partial class and initialize the GUID in the OnCreated () event (which does not use the default value newid (), but solves the problem:

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

Note that for existing objects (populated by the request) this value will be overwritten with the correct value when processing OnLoaded ().

Answer 2

Hey Kevin, a quick question might be silly, but just to get a better idea ...

- any of the properties that you set on the new change change object, the Primary Key property?

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

amuses

EDIT:

thanks Kevin. What happens to this line here:

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

What restriction do you have on the PC ...? Why it does not look like this:

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

?

EDIT Hello, Kevin. I think the problem is that you did not set your PC as a qualifier identifier, and therefore PK did not automatically increase it.

To fix the problem, recreate the table by changing this line:

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

:

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

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

Remember to also update your DBML LINQ 2 SQL

Answer 3

What is your primary key? And do you have any other keys / restrictions on the table? This is probably one of the problems that causes the problem. I suggest expanding the search for matching entries or changing restrictions.