title: EF updating an existing entity with new reference and get Violation of UNIQUE KEY constraint
body: <p>I'm trying to update an existing in the DB entity (MamConfiguration_V1) call it Parent</p>
  
  <p>I want to</p>
  
  <p>1) add a reference (navigation) member to it (MamConfigurationToBrowser_V1) call it Child</p>
  
  <p>2) I don't change the value of a unique property in the Parent entity. (Parent.Name)</p>
  
  <p>I got the entity to update from the DB,</p>
  
  <p>and using the same context (mMamDbEntities)</p>
  
  <p>I add the reference member.</p>
  
  <p>I don't change its Name property.</p>
  
  <p>However I get a DB error, uniquness restriction violation</p>
  
  <pre><code>{"Violation of UNIQUE KEY constraint 'UQ_MamConfigurations_V1'. Cannot insert duplicate key in object 'dbo.MamConfiguration_V1'. The duplicate key value is (elad_14Apr_1315).\r\nThe statement has been terminated."}
  </code></pre>
  
  <p>I did DB sniffing and saw the generated code:</p>
  
  <pre><code>exec sp_executesql N'update [dbo].[MamConfiguration_V1]
  set [Name] = @0, [Description] = @1, [StatusId] = @2, [Type] = @3, [UpdatedDate] = @4, [PercentageTraffic] = @5, [NumericTraffic] = @6
  where ([ConfigurationId] = @7)
  ',N'@0 nvarchar(50),@1 nvarchar(200),@2 int,@3 int,@4 datetime2(7),@5 int,@6 int,@7 int',@0=N'elad_17Apr_1120_tmpToRemove_',@1=N'elad_22Apr_1120',@2=2,@3=0,@4='2013-04-23 11:17:25.4991650',@5=55,@6=-1,@7=32
  </code></pre>
  
  <p>//some more commands</p>
  
  <pre><code>exec sp_executesql N'insert [dbo].[MamConfiguration_V1]([Name], [Description], [StatusId], [Type], [CreatedDate], [UpdatedDate], [PercentageTraffic], [NumericTraffic])
  values (@0, @1, @2, @3, @4, @5, @6, @7)
  select [ConfigurationId]
  from [dbo].[MamConfiguration_V1]
  where @@ROWCOUNT &gt; 0 and [ConfigurationId] = scope_identity()',N'@0 nvarchar(50),@1 nvarchar(200),@2 int,@3 int,@4 datetime2(7),@5 datetime2(7),@6 int,@7 int',@0=N'elad_17Apr_1120_tmpToRemove_',@1=N'elad_22Apr_1120',@2=2,@3=0,@4='0001-01-01 00:00:00',@5='0001-01-01 00:00:00',@6=55,@7=-1
  </code></pre>
  
  <p>I would have expected  upsert to be <code>@@ROWCOUNT = 0</code></p>
  
  <p>What am I doing wrong?</p>
  
  <p>My code and DB are:</p>
  
  <pre><code>        public void SaveCofiguration(MamConfiguration_V1Ui itemUi)
          {
              var itemEf = mMamConfiguration_V1UiToEfConvertor.ConvertToNewEf(itemUi);
  
              using (var maMDBEntities = new MaMDBEntities())
              {
                  IDal&lt;MamConfiguration_V1&gt; mamConfigurationDal = mDalFactory.GetDal&lt;MamConfiguration_V1&gt;(maMDBEntities);
  
                  mamConfigurationDal.Save(itemEf);
              }
          }
  
           public MamConfiguration_V1 GetById(object id)
          {           
                  id.ThrowIfNull("id");
  
                  int configurationId = Convert.ToInt32(id);
  
                  var result =
                      mMaMDBEntities.MamConfiguration_V1.SingleOrDefault(item =&gt; item.ConfigurationId == configurationId);
  
                  return result;
  
          }
  
         public MamConfiguration_V1 Save(MamConfiguration_V1 item)
          {
  
                  item.ThrowIfNull("item");
  
                  var itemFromDB = GetById(item.ConfigurationId);
  
                  if (itemFromDB != null)
                  {
                      UpdateEfItem(itemFromDB, item);
  
                     // if (mMaMDBEntities.ObjectStateManager.GetObjectStateEntry(itemFromDB).State == EntityState.Detached)
  //                    {
    //                      mMaMDBEntities.MamConfiguration_V1.AddObject(itemFromDB);
      //                }
  
                      // Attached object tracks modifications automatically
                      mMaMDBEntities.SaveChanges();
  
                      return item;
                  }
  
  
  
  
  
         private void UpdateEfItem(MamConfiguration_V1 itemFromDb, MamConfiguration_V1 itemFromUi)
              {
                  itemFromDb.UpdatedDate = DateTime.Now;
  
                  itemFromDb.Description = itemFromUi.Description;
  
                  itemFromDb.StatusId = itemFromUi.StatusId;
  
                  itemFromDb.Name = itemFromUi.Name;
  
                  itemFromDb.NumericTraffic = itemFromUi.NumericTraffic;
  
                  itemFromDb.PercentageTraffic = itemFromUi.PercentageTraffic;
  
                  itemFromDb.Type = itemFromUi.NumericTraffic;
  
                  foreach (var item in itemFromDb.MamConfigurationToBrowser_V1.ToList())
                  {
                      if (itemFromUi.MamConfigurationToBrowser_V1.All(b =&gt; b.BrowserVersionId != item.BrowserVersionId))
                      {
                          mMaMDBEntities.MamConfigurationToBrowser_V1.DeleteObject(item);
                      }
                  }
  
                  for (int i = 0; i &lt; itemFromUi.MamConfigurationToBrowser_V1.Count; i++)
                  {
                      var element = itemFromUi.MamConfigurationToBrowser_V1.ElementAt(i);
                      var item = itemFromDb.MamConfigurationToBrowser_V1.SingleOrDefault(b =&gt; b.BrowserVersionId == element.BrowserVersionId);
                      if (item != null)
                      {
                          // copy properties from element to item
                      }
                      else
                      {
                          element.Browser = mMaMDBEntities.Browsers.Single(browserItem =&gt;
                              browserItem.BrowserID == element.BrowserID);
  
                          //element.MamConfiguration_V1 = itemFromDb;
  
                          //have also tried: element.MamConfiguration_V1 = null;
  
                          //element.MamConfiguration_V1Reference = null;
  
                          itemFromDb.MamConfigurationToBrowser_V1.Add(element);
                      }
                  }
              }
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p><code>mMaMDBEntities.SaveChanges();</code> throws the exception</p>
  
  <p>These are the DB tables:</p>
  
  <pre><code>CREATE TABLE [dbo].[MamConfiguration_V1](
      [ConfigurationId] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NOT NULL,
      [Description] [nvarchar](200) NOT NULL,
      [StatusId] [int] NOT NULL,
      [Type] [int] NOT NULL,
      [CreatedDate] [datetime2](7) NOT NULL,
      [UpdatedDate] [datetime2](7) NOT NULL,
      [PercentageTraffic] [int] NOT NULL,
      [NumericTraffic] [int] NOT NULL,
   CONSTRAINT [PK_MamConfigurations_V1] PRIMARY KEY CLUSTERED 
  (
      [ConfigurationId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY],
   CONSTRAINT [UQ_MamConfigurations_V1] UNIQUE NONCLUSTERED 
  (
      [Name] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY]
  ) ON [SECONDARY]
  
  GO
  
  ALTER TABLE [dbo].[MamConfiguration_V1]  WITH CHECK ADD  CONSTRAINT [FK_MamConfiguration_V1_ConfigurationType_V1] FOREIGN KEY([StatusId])
  REFERENCES [dbo].[MamConfigurationStatuses] ([StatusId])
  GO
  
  ALTER TABLE [dbo].[MamConfiguration_V1] CHECK CONSTRAINT [FK_MamConfiguration_V1_ConfigurationType_V1]
  GO
  
  ALTER TABLE [dbo].[MamConfiguration_V1] ADD  CONSTRAINT [DF_MamConfigurations_V1_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
  GO
  
  ALTER TABLE [dbo].[MamConfiguration_V1] ADD  CONSTRAINT [DF_MamConfigurations_V1_UpdatedDate]  DEFAULT (getdate()) FOR [UpdatedDate]
  GO
  
  
  
  
  CREATE TABLE [dbo].[MamConfigurationToBrowser_V1](
      [MamConfigurationId] [int] NOT NULL,
      [BrowserVersionId] [uniqueidentifier] NOT NULL,
      [IsWhiteListed] [bit] NOT NULL,
      [BrowserID] [int] NOT NULL,
      [VersionNumberLowRange] [varchar](50) NOT NULL,
      [CreatedDate] [datetime] NOT NULL,
      [UpdatedDate] [datetime] NOT NULL,
      [VersionNumberUpperRange] [varchar](50) NULL,
   CONSTRAINT [PK_MamConfigurationToBrowser_V1_1] PRIMARY KEY CLUSTERED 
  (
      [BrowserVersionId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [SECONDARY]
  ) ON [SECONDARY]
  
  GO
  
  SET ANSI_PADDING OFF
  GO
  
  ALTER TABLE [dbo].[MamConfigurationToBrowser_V1]  WITH CHECK ADD  CONSTRAINT [FK_MamConfigurationToBrowser_V1_Browsers] FOREIGN KEY([BrowserID])
  REFERENCES [dbo].[Browsers] ([BrowserID])
  GO
  
  ALTER TABLE [dbo].[MamConfigurationToBrowser_V1] CHECK CONSTRAINT [FK_MamConfigurationToBrowser_V1_Browsers]
  GO
  
  ALTER TABLE [dbo].[MamConfigurationToBrowser_V1]  WITH CHECK ADD  CONSTRAINT [FK_MamConfigurationToBrowser_V1_BrowserVersion] FOREIGN KEY([MamConfigurationId])
  REFERENCES [dbo].[MamConfiguration_V1] ([ConfigurationId])
  GO
  
  ALTER TABLE [dbo].[MamConfigurationToBrowser_V1] CHECK CONSTRAINT [FK_MamConfigurationToBrowser_V1_BrowserVersion]
  GO
  
  ALTER TABLE [dbo].[MamConfigurationToBrowser_V1] ADD  CONSTRAINT [DF_Browser_V1_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
  GO
  
  ALTER TABLE [dbo].[MamConfigurationToBrowser_V1] ADD  CONSTRAINT [DF_Browser_V1_UpdatedDate]  DEFAULT (getdate()) FOR [UpdatedDate]
  GO
  </code></pre>
  
  <p><strong>Update 2</strong></p>
  
  <p>I have tried @AzharKhorasany  solution, but go the same error:</p>
  
  <pre><code>for (int i = 0; i &lt; itemFromUi.MamConfigurationToBrowser_V1.Count; i++)
              {
                  var element = itemFromUi.MamConfigurationToBrowser_V1.ElementAt(i);
                  var item = itemFromDb.MamConfigurationToBrowser_V1.SingleOrDefault(b =&gt; b.BrowserVersionId == element.BrowserVersionId);
                  if (item != null)
                  {
                      // copy properties from element to item
                  }
                  else
                  {
                      element.Browser = mMaMDBEntities.Browsers.Single(browserItem =&gt;
                          browserItem.BrowserID == element.BrowserID);
  
                      element.MamConfigurationId = itemFromDb.ConfigurationId;
  
                      //element.MamConfiguration_V1 = itemFromDb;
  
                      //have also tried: element.MamConfiguration_V1 = null;
  
                      //element.MamConfiguration_V1Reference = null;
                      //mMaMDBEntities.AddToMamConfigurationToBrowser_V1(itemFromUi.MamConfigurationToBrowser_V1.ElementAt(0)); // add as inserted
  
                      itemFromDb.MamConfigurationToBrowser_V1.Add(element);
                  }
              }
  </code></pre>