none
Entity Framework - Using my own Primary Key RRS feed

  • Question

  • Hello,

    I try to learn some Entity Framework, I need a help -  I have table with Primary Key without auto-incremet. So before save new record I need to read last used ID and add 1. I can do it like this:

    Dim NewId As Long = _entity.MyTable.Max(Function(u) u.Id) + 1

    But there is a chance that between creating _entity object and saving new record somebody added new records to database and this function return incorrect id. So just before save new data I tried to refresh data in my _entity:

    _entity.Refresh(Objects.RefreshMode.ClientWins, _entity)

    Unfortunately this function throws exception "objects to refresh has a null EntityKey property value". (Sure, Key is null because I just want to create it)

    Of course I can read last Id directly from database (without Entity Framework), but I hope there is a better way to do it...?

    Thanks in advance for help!

    Wednesday, November 20, 2013 12:12 PM

Answers

  • Hi,

    Maybe you can simulate Oracle sequences creating a table more or less like this:

    SEQUENCE:

    [Table/Key] [Value/ID]

    Then you just need to create an stored procedure, for example, that reads a given key's value, add 1 and returns this new Id value.

    So in this way, all process need to call this procedure and teh new Id is returned and cosumed without any collision risk.

    Hope this could help you.

    Regards.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.


    • Edited by JA Reyes Wednesday, November 20, 2013 1:11 PM
    • Marked as answer by Fred BaoModerator Thursday, November 28, 2013 9:20 AM
    Wednesday, November 20, 2013 1:11 PM
  • > hope there is a better way to do it...?

    Yes.  Let the database generate the keys for you with IDENTITY or SEQUENCE.  If you use a SEQUENCE you can fetch new values from the client if you want.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, November 20, 2013 4:06 PM
  • Hello,

    >>But there is a chance that between creating _entity object and saving new record somebody added new records to database and this function return incorrect id

    How will somebody add new records to database?

    If by program, so the application should be a multiple-user program. If it is, I suggest you adding a transaction for the application to lock the table so that we will get the correct id before saving new records.

    For transaction in entity framework, please have a look link below:

    http://msdn.microsoft.com/en-us/library/vstudio/bb738523(v=vs.100).aspx

    If somebody adds new records to database manually, I think we should avoid this operation in business.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 21, 2013 6:31 AM
    Moderator

All replies

  • Hi,

    Maybe you can simulate Oracle sequences creating a table more or less like this:

    SEQUENCE:

    [Table/Key] [Value/ID]

    Then you just need to create an stored procedure, for example, that reads a given key's value, add 1 and returns this new Id value.

    So in this way, all process need to call this procedure and teh new Id is returned and cosumed without any collision risk.

    Hope this could help you.

    Regards.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.


    • Edited by JA Reyes Wednesday, November 20, 2013 1:11 PM
    • Marked as answer by Fred BaoModerator Thursday, November 28, 2013 9:20 AM
    Wednesday, November 20, 2013 1:11 PM
  • > hope there is a better way to do it...?

    Yes.  Let the database generate the keys for you with IDENTITY or SEQUENCE.  If you use a SEQUENCE you can fetch new values from the client if you want.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, November 20, 2013 4:06 PM
  • Hello,

    >>But there is a chance that between creating _entity object and saving new record somebody added new records to database and this function return incorrect id

    How will somebody add new records to database?

    If by program, so the application should be a multiple-user program. If it is, I suggest you adding a transaction for the application to lock the table so that we will get the correct id before saving new records.

    For transaction in entity framework, please have a look link below:

    http://msdn.microsoft.com/en-us/library/vstudio/bb738523(v=vs.100).aspx

    If somebody adds new records to database manually, I think we should avoid this operation in business.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 21, 2013 6:31 AM
    Moderator