none
Duplicate Entries RRS feed

  • Question

  • I've been struggling with this issue for quite some time now in which my code creates duplicate entries.  This does not happen all the time but it happens and I’d like to resolve it.

    In my code, I’m removing and/or adding an object(s) based upon an array of removed ids and a "List (Of" object type within a "Using" statement.  I need to rollback the transaction if any of the processes fail to write to the database.   I’m currently using EF 4.1.1 and MVC 3 with a repository pattern.  Here’s the questionable code:

    Code

    Public Function RemoveAndOrSaveMATs(methodactions As List(Of MAT), matIds As String(), removedBy As String, comment As ExternalComment) As Boolean Implements IEntityRepository.RemoveAndOrSaveMATs

            Dim retBool As Boolean = False

            ' Save the data

            Using db As New Entities()

                'Remove the flagged methodactions

                If matIds IsNot Nothing Then

                    For Each matId In matIds

                        'Update the status flag in the main table

                        Dim mat = db.MAT.CreateObject()

                        mat.id = CInt(matId)

                        db.MAT.Attach(mat)

                        mat.statusId = 4

                        'Insert the removed id into the relational table

                        Dim mar As MAR = New MAR()

                        mar.removedId = CInt(matId)

                        mar.removedBy = removedBy

                        mar.removedOnDt = Date.Now()

                        db.MARs.AddObject(mar)

                    Next

                End If

                'Insert the new methodactions

                For Each ma In methodactions

                    'TODO: Refactor code to account for the state of the entity and all objects in its object tree.  For now, utilize an insert stored procedure to avoid duplicate entries  (This workaround is still creating duplicate entries).

                    'db.MATs.AddObject(ma)

                    db.InsertMAT(ma.userId, ma.methodId, ma.actionId, ma.comments, ma.followUpDt, ma.statusId, ma.methodActionDt)

                Next

                'If the external comment is nothing, then commit the database changes

                If comment Is Nothing Then

                    db.SaveChanges()

                    retBool = True

                Else

                    'If the external comment is successfully added to the external process, commit the database changes

                    If (AddExternalComment(comment)) Then

                        db.SaveChanges()

                        retBool = True

                    End If

                End If

            End Using

            Return retBool

        End Function

    Any ideas?



    • Edited by Cyberhornet Monday, September 16, 2013 10:18 PM Correction
    Monday, September 16, 2013 5:30 PM

Answers

  • Hi,

    For this issue, my suggestion is to do a check before inserting data into the database in program or the procedure like below:

    For Each ma In methodactions

    'check whether the record already exists

    If Check(ma) ==true then

    db.MATs.Add(ma)

    EndIf

    Next

    I guess this is caused by:

    1.The methodactions list may exist two or more records which have the same value.

    2.There are two or more users adding the same record at one time.

    Regards.

     

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 20, 2013 8:42 AM
    Moderator

All replies

  • Hi,

    Could you please share the table structure of the MAT and MAR.

    So that I think your probelm will have a better solution.

    Thanks.

    Tuesday, September 17, 2013 12:44 PM
  • Yes.

    CREATE TABLE [dbo].[MAT](

         [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

         [moduleId] [numeric](18, 0) NOT NULL,

         [userId] [nvarchar](20) NOT NULL,

         [methodId] [numeric](18, 0) NOT NULL,

         [actionId] [numeric](18, 0) NULL,

         [comments] [nvarchar](30) NULL,

         [followUpDt] [date] NOT NULL,

         [followUpStatusId] [int] NOT NULL,

         [methodActionDt] [datetime] NOT NULL,

     CONSTRAINT [PK_MAT] PRIMARY KEY CLUSTERED

    (

         [id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[MAT]  WITH CHECK ADD  CONSTRAINT [FK_MAT_Method] FOREIGN KEY([methodId])

    REFERENCES [dbo].[Method] ([id])

    GO

    ALTER TABLE [dbo].[MAT] CHECK CONSTRAINT [FK_MAT_Method]

    GO

    ALTER TABLE [dbo].[MAT]  WITH CHECK ADD  CONSTRAINT [FK_MAT_FollowUpStatus] FOREIGN KEY([followUpStatusId])

    REFERENCES [dbo].[FollowUpStatus] ([id])

    GO

    ALTER TABLE [dbo].[MAT] CHECK CONSTRAINT [FK_MAT_FollowUpStatus]

    GO

    CREATE TABLE [dbo].[MAR](

         [id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

         [removedId] [numeric](18, 0) NOT NULL,

         [removedBy] [nvarchar](20) NOT NULL,

         [removedOnDt] [datetime] NOT NULL,

     CONSTRAINT [PK_MAR] PRIMARY KEY CLUSTERED

    (

         [id] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[MAR]  WITH CHECK ADD  CONSTRAINT [FK_MAR_MAT] FOREIGN KEY([removedId])

    REFERENCES [dbo].[MAT] ([id])

    GO

    ALTER TABLE [dbo].[MAR] CHECK CONSTRAINT [FK_MAR_MAT]

    GO

     

     



    • Edited by Cyberhornet Tuesday, September 17, 2013 2:35 PM Correction
    Tuesday, September 17, 2013 2:32 PM
  • Hello,

    Thanks for sharing the table structure.

    However I am a bit confused that whether the statusId is a column, because I do have a status column, so I change it to followUpStatusId =4.

    With the code, I made a sample like below and then it runs ok:

    Public Function RemoveAndOrSaveMATs(methodactions As List(Of MAT), matIds As String(), removedBy As String) As Boolean
    
            Dim retBool As Boolean = False
    
            ' Save the data
    
            Using db As New VBDBEntities()
    
    
                'Remove the flagged methodactions
    
                If matIds IsNot Nothing Then
    
    
                    For Each matId In matIds
    
    
                        'Update the status flag in the main table
    
                        Dim mat = db.MATs.Create()
    
                        mat.id = CInt(matId)
    
                        db.MATs.Attach(mat)
    
                        mat.followUpStatusId = 4
    
    
                        'Insert the removed id into the relational table
    
                        Dim mar As MAR = New MAR()
    
                        mar.removedId = CInt(matId)
    
                        mar.removedBy = removedBy
    
                        mar.removedOnDt = Date.Now()
    
                        db.MARs.Add(mar)
    
                    Next
    
                End If
    
                'Insert the new methodactions
    
                For Each ma In methodactions
    
                    'TODO: Refactor code to account for the state of the entity and all objects in its object tree.  For now, utilize an insert stored procedure to avoid duplicate entries  (This workaround is still creating duplicate entries).
    
                    db.MATs.Add(ma)
    
                    'db.InsertMAT(ma.userId, ma.methodId, ma.actionId, ma.comments, ma.followUpDt, ma.followUpStatusId, ma.methodActionDt)
    
                Next
    
                'If the external comment is nothing, then commit the database changes
    
                db.SaveChanges()
    
            End Using
    
            Return retBool
    
    End Function
    

    It does not create duplicate entities.

    Maybe my data is created to not be what you have created.

    And I am confused how the MAT table will have duplicate records, because its key is indentity.

    Or if it means all columns have same value except the key filed like below:

    If it is the second, it maybe that you insert the exist object except primary key.

    So could you please share what is the duplicate records.

    Thanks &Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, September 18, 2013 5:59 AM
    Moderator
  • Yes, it's the second scenario.  I have several records in my table with the same values (except for the id data column and the timestamps are milliseconds apart).   My understanding is that the object graph does not create an id until its inserted into the actual table during the "SaveChanges()" function.  But, that does not explain why I'm getting multiple inserts of the same data.  I should mention that I have more than 20 users utilizing this process at one time.  Still, concurrent users should not have any effect.

    As for the statusId data column, you're assumption was correct.  I had to change my code for proprietary and confidentiality reasons.

    Wednesday, September 18, 2013 2:13 PM
  • Hi,

    For this issue, my suggestion is to do a check before inserting data into the database in program or the procedure like below:

    For Each ma In methodactions

    'check whether the record already exists

    If Check(ma) ==true then

    db.MATs.Add(ma)

    EndIf

    Next

    I guess this is caused by:

    1.The methodactions list may exist two or more records which have the same value.

    2.There are two or more users adding the same record at one time.

    Regards.

     

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Friday, September 20, 2013 8:42 AM
    Moderator