title: InsertOnSubmit = Cannot add an entity with a key that is already in use
body: <p>I am trying to insert a record into a table using Linq but get the dreaded <strong><em>Cannot add an entity with a key that is already in use</em></strong> error</p>
  
  <pre><code>'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 &gt; 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("&lt;" &amp; DateTime.Now.ToString &amp; "&gt; " &amp; "Error - creating risk change record in dataset for patient " &amp; Drow.Item("NHI").ToString() &amp; " - " &amp; ex.Message)
      End Try
  
  End If
  </code></pre>
  
  <p>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.</p>
  
  <p>The SubmitChanges function is call a few lines down. </p>
  
  <p>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).</p>
  
  <p>Cheers in advance.</p>
  
  <hr>
  
  <p>this is the create script for the table:</p>
  
  <pre><code>USE [CDEvolution]
  </code></pre>
  
  <p>GO</p>
  
  <p>/****** Object:  Table [dbo].[RISKCHANGES]    Script Date: 05/13/2009 14:40:15 ******/</p>
  
  <p>SET ANSI_NULLS ON</p>
  
  <p>GO</p>
  
  <p>SET QUOTED_IDENTIFIER ON</p>
  
  <p>GO</p>
  
  <p>SET ANSI_PADDING ON</p>
  
  <p>GO</p>
  
  <p>CREATE TABLE [dbo].[RISKCHANGES](</p>
  
  <pre><code>[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]  
  </code></pre>
  
  <p>DEFAULT (newid()),</p>
  
  <p>CONSTRAINT [PK_RISKCHANGES] PRIMARY KEY CLUSTERED </p>
  
  <p>(</p>
  
  <pre><code>[IDENTITY] ASC
  </code></pre>
  
  <p>)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]</p>
  
  <p>) ON [PRIMARY]</p>
  
  <p>GO</p>
  
  <p>SET ANSI_PADDING OFF</p>