none
Update Model from Database : Error Message : An item with the same key has already been added. RRS feed

  • Question

  • Hello,

    I am working on  .Net Entity Framework driver development and i am facing an error when i try to update the model from database.

    I created 2 over loaded stored procedures say for example:

    SP1(IN p1 INT, OUT p2 INT)

    SP2(IN P1 INT, IN p2 INT, OUT p3 INT)

    in the local database.

    When i try to update the model, i get the following error message : "An item with the same key has already been added."

    The moment i delete one of the stored procedure say SP1 or SP2, i am able to update the model successfully.

    I do not have control over these keys generated since it all happens internal to EF runtime.

    Please help me in getting this issue resolved.

    Thanks and Regards,

    Prashanth.

    Wednesday, April 10, 2013 10:37 AM

Answers

  • Hi Prashanth,

    I have to let you know, it is a limitation of the entity framework -- duplicated stored procedure name is not supported. Generally, we will suggest customers remove the duplicated names to solve the issue.

    From my understanding, you are trying to let Microsoft change the limitation. I will suggest you contact Microsoft paid support at http://support.microsoft.com . It is because your request is beyond what we can do on forum.

    Thanks for your understanding.


    Alan Yao [MSFT]
    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.

    Monday, April 22, 2013 6:15 AM

All replies

  • Hi Prashanth,

    I tried to create the stored procedures but unfortunately I cannot reproduce this problem.

    Would you like check the SSDL about how the stored procedures are represented? If you have mapped the stored procedures, would you like to also check the MSL part?

    Here is a thread talking about designer problem with the error "An item with the same key has already been added". You can try whether EdmGen.exe will help you to solve the problem.

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/cf0d9018-df12-4693-8b2f-9568e2e0168c

    Would you like to also upload a sample to SkyDrive? It will help us to understand the problem.

    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.

    Friday, April 12, 2013 7:41 AM
    Moderator
  • Hello Chester,

    Can you please let me know against which database server you created the overloaded stored procedures ?

    In my previous mail thread, there was a type error, both the stored procedure names should be same i.e SP1

    Please follow the below steps to reproduce the problem :
    1) create a database :  sample

    2) create a table in the sample database : create table tab1(col1 integer, col2 integer)

    3) create a procedure in the sample database
    create procedure sampProc language SQL P1: BEGIN return 100; END P1

    4) create a visual studio 2010 windows console project.

    5) add an ADONET enity model and select the table tab1 and procedure sampProc and create the model.

    6) Once the model is created, now create another stored procedure which is of the same name as the previously created stored procedure i.e sampProc :
    create procedure sampProc(IN idno INT, OUT empsal DOUBLE) language SQL P1: BEGIN return 100; END P1

    7) Now right click on the generated model and select the option : " Update Model from Database"
    now you will get the error

    Please let me know if you have any further questions.

    Thanks and Regards,

    Prashanth.

    Tuesday, April 16, 2013 6:00 AM
  • Hi Prashanth,

    Thanks for the information. I'm actually using SQL Server 2012.

    This is what I used to create numbered stored procedures:

    CREATE PROCEDURE sampProc;1
    AS
    BEGIN
    	return 100
    END

    and 

    CREATE PROCEDURE sampProc;2
    @param1 int, @param2 int out
    AS
    BEGIN
    	return 100
    END

    However only the first stored procedure is generated. In fact, numbered stored procedure will be deprecated and is considering to be removed in future versions of SQL Sever. I'll try to test on SQL Server 2008 and let you know the result.

    What database server are you using? Is it db2 or others?

    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.

    Thursday, April 18, 2013 2:57 AM
    Moderator
  • Hi Chester,

    I am using DB2 database.

    Against DB2 database overload procedures are created with the same name and different number of parameters as shown in my previous mail thread.

    I understand that you are using the feature "numbered stored procedures" in order to create overloaded stored procedures.

    is numbered stored procedures same as creating db2 overloaded stored procedures ?

    Thanks and Regards,

    Prashanth.

    Thursday, April 18, 2013 5:46 AM
  • Hi Prashanth,

    I think you are right. In fact SQL Server doesn't support overloaded stored procedures. So EF runtime doesn't support it either. It is just a workaround to use numbered stored procedures. 

    Are you using DB2 Data Providers from IBM? If so, I think you may try IBM forum to see whether this problem has been solved. Here is a threads I found with the same "An item with the same key has already been added" issue:

    https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014289048

    we're currently looking into how we can support this. Unfortunately, the solution to this issue is not simple. The SQL Server syntax doesn't support overloaded SP definition, and, thus, the EF runtime that they built doesn't support it either. Thus, it expects the SCHEMA+SPNAME combination to be unique. We also can't simply change the SP names to be unique in our queries, because then they would not be callable. We will work with microsoft to see how we can resolve this.

    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.

    Thursday, April 18, 2013 8:44 AM
    Moderator
  • Hi Chester,

    I am actually working on IBM DB2 Data Providers and i am also aware of the dwforum question which was replied by our team member.

    After further investigation we found that our provider does not have any control on these keys which are being generated by EF, which are internal to it.

    So if you can make these keys unique for overload stored procedures while updating the model, it could solve the issue.

    Thanks and Regards,

    Prashanth.

    Friday, April 19, 2013 6:07 AM
  • Hi Prashanth,

    I have to let you know, it is a limitation of the entity framework -- duplicated stored procedure name is not supported. Generally, we will suggest customers remove the duplicated names to solve the issue.

    From my understanding, you are trying to let Microsoft change the limitation. I will suggest you contact Microsoft paid support at http://support.microsoft.com . It is because your request is beyond what we can do on forum.

    Thanks for your understanding.


    Alan Yao [MSFT]
    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.

    Monday, April 22, 2013 6:15 AM
  • Hi Alan,

    Thanks for letting me know the limitation.

    It will be great full if you can point me to the Microsoft link which tells me about this limitation.

    Thanks and Regards,

    Prashanth.

    Wednesday, April 24, 2013 1:48 PM
  • Hi Prashanth,

    There is no document about this specific issue.

    The "An item with the same key has already been added." error usually occurs when adding a duplicate key into Dictionary<TKey, TValue> object. And per my investigation, the generation tool uses Stored procedure name as the key part. So to change the current EntityModel generation design, as I suggested, you need to contact paid support at http://support.microsoft.com to see if it is possible to make the change happen.

    Thanks,


    Alan Yao [MSFT]
    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.


    Thursday, April 25, 2013 9:53 AM