none
Mapped stored procedure adds each row twice RRS feed

  • Question

  • Hi All,

    I hope I picked the right forum as I'm not 100% sure where my problem comes from.

    I have the situation that I need to insert data provided by a VB.NET application into a temp database with only one table. The data will then be moved into different other tables on the productive system. I implemented everything fine so far and inserting data into the table works quite fine and nice. Now I have the requirement to use a Stored Procedure for inserting the data, so I created the following SP:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE usp_AddTempStuff
    	-- Add the parameters for the stored procedure here
    	@Serial nvarchar(50),
    	@Name nvarchar(25),
    	@Stuff nvarchar(50),
    	@SomeID nvarchar(90),
    	@Somethingelse nvarchar(240),
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	--Insert current record into the Temp Table
    	INSERT into tempTable (Serial, Name, Stuff, SomeID, Somethingelse)
    	values (@Serial, @Name, @Stuff, @SomeID, @Somethingelse)
    END
    



    The only thing that is not being taken care of in the SP is an ID column which has the datatype BIGINT and identity(1,1).

    I've mapped this Stored Procedure with the Insert function on the temp table using the Stored Procedure Mapping of my DataWarehouse.edmx file.

    If I save the data using the following code I get the message: AcceptMessage cannot continue because the object's key values conflict with another object in the ObjectState Manager

    DIM context as DataWarehouseEntities context = new DataWarehouseEntities For each result in results 'create a new entry for the tempEntry EF Object DIM entry as new tempEntry() 'assign all entry variables entry.name = result.name ..... 'save the new entry context.AddObject("tempEntries", entry) context.SaveChanges()

    Next

    Aside of the Error message that I get it enters 2-3 entries without any duplicates (but not all results are being added to the table)

    If I now change the context.SaveChanges() to context.SaveChanges(SaveOptions.DetectChangesBeforeSave) it will enter duplicate entries for most of the results (but not all). Can you please help me or give me a hint where my problem is? 

    Wednesday, May 30, 2012 9:35 AM

Answers

  • Hi,

    Could it be that as you don't read back the identity column it is always 0 client side and you ends up with multiple entities with the same key ? See http://blogs.msdn.com/b/adonet/archive/2008/03/26/stored-procedure-mapping.aspx that shows how to SELECT SCOPE_IDENTITY() AS YourIDColumnName after the INSERT so that EF can read back the new id and update the client side object.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by hablamer Wednesday, May 30, 2012 11:31 AM
    Wednesday, May 30, 2012 10:56 AM

All replies

  • Hi,

    Could it be that as you don't read back the identity column it is always 0 client side and you ends up with multiple entities with the same key ? See http://blogs.msdn.com/b/adonet/archive/2008/03/26/stored-procedure-mapping.aspx that shows how to SELECT SCOPE_IDENTITY() AS YourIDColumnName after the INSERT so that EF can read back the new id and update the client side object.


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    • Marked as answer by hablamer Wednesday, May 30, 2012 11:31 AM
    Wednesday, May 30, 2012 10:56 AM
  • Hi Patrice Scribe,

    That did solve it. Thank you :-)

    Weirdly enough the duplicated entries always had different IDs in the database (which is to be expected as it's an auto increment key).
    I don't really get why the EF needs to have the keys of the rows as it should not care but that solves the issue and now I know it for the future

     
    • Edited by hablamer Wednesday, May 30, 2012 11:34 AM
    Wednesday, May 30, 2012 11:31 AM