locked
EF @@ROWCOUNT > 0 leads to wrong upsert RRS feed

  • Question

  • I'm trying to update an existing in the DB entity (MamConfiguration_V1) call it Parent

    I want to

    1) add a reference (navigation) member to it (MamConfigurationToBrowser_V1) call it Child

    2) I don't change the value of a unique property in the Parent entity. (Parent.Name)

    I got the entity to update from the DB,

    and using the same context (mMamDbEntities)

    I add the reference member.

    I don't change its Name property.

    However I get a DB error, uniquness restriction violation

        {"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."}



    I did DB sniffing and saw the generated 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
        
    //some more commands
        
        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 > 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
                                                                                                                                                                                           






    I would have expected  upsert to be `@@ROWCOUNT = 0`

    What am I doing wrong?

    My code and DB are:

                public void SaveCofiguration(MamConfiguration_V1Ui itemUi)
                {
                    var itemEf = mMamConfiguration_V1UiToEfConvertor.ConvertToNewEf(itemUi);
    
                    using (var maMDBEntities = new MaMDBEntities())
                    {
                        IDal<MamConfiguration_V1> mamConfigurationDal = mDalFactory.GetDal<MamConfiguration_V1>(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 => 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 => b.BrowserVersionId != item.BrowserVersionId))
                            {
                                mMaMDBEntities.MamConfigurationToBrowser_V1.DeleteObject(item);
                            }
                        }
    
                        for (int i = 0; i < itemFromUi.MamConfigurationToBrowser_V1.Count; i++)
                        {
                            var element = itemFromUi.MamConfigurationToBrowser_V1.ElementAt(i);
                            var item = itemFromDb.MamConfigurationToBrowser_V1.SingleOrDefault(b => b.BrowserVersionId == element.BrowserVersionId);
                            if (item != null)
                            {
                                // copy properties from element to item
                            }
                            else
                            {
                                element.Browser = mMaMDBEntities.Browsers.Single(browserItem =>
                                    browserItem.BrowserID == element.BrowserID);
    
                                //element.MamConfiguration_V1 = itemFromDb;
    
                                //have also tried: element.MamConfiguration_V1 = null;
    
                                //element.MamConfiguration_V1Reference = null;
    
                                itemFromDb.MamConfigurationToBrowser_V1.Add(element);
                            }
                        }
                    }
    


    `mMaMDBEntities.SaveChanges();` throws the exception

    These are the DB tables:


        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
       




    **Update 2**

    I have tried the following  solution, but go the same error:

     for (int i = 0; i < itemFromUi.MamConfigurationToBrowser_V1.Count; i++)
                    {
                        var element = itemFromUi.MamConfigurationToBrowser_V1.ElementAt(i);
                        var item = itemFromDb.MamConfigurationToBrowser_V1.SingleOrDefault(b => b.BrowserVersionId == element.BrowserVersionId);
                        if (item != null)
                        {
                            // copy properties from element to item
                        }
                        else
                        {
                            element.Browser = mMaMDBEntities.Browsers.Single(browserItem =>
                                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);
                        }
                    }




    • Edited by elad2109 Tuesday, April 23, 2013 11:22 AM
    Tuesday, April 23, 2013 11:21 AM

Answers

  • Hi elad2109,

    I suppose what you want is to update the existing record rather than insert a new record.

    I you just want to update the entity, it will be enough for EF to track the changes after setting the properties to that entity. Add method is used to insert a new record to the context. After you call the Add method, another record with the same Name will be inserted, however the ConfigurationId will be determined by database.

    I would suggest you try to comment out the Add statement to see whether it is what you want.

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Chester Hong Tuesday, May 7, 2013 9:25 AM
    Friday, April 26, 2013 3:07 AM