none
Use of Entity Frameworks and generation of primary keys from Primary Key Pool RRS feed

  • Question

  • Hello,

    In some cases, autoincrement default columns values cannot be used:

    1) E.g. distributed database of Mobile devices (eg. remote sales people) and Consolidated database
    To avoid conflicts, a primary key pool can be used: a table that holds a set of primary key values (for each remote database a certain amount of keys).
    The key pools are managed by the consolidated database. When a remote users connects, the primary keys in the remote database are 'refilled'

    2) Business processes consisting of reuse of parent-child business objects:
    e.g. for making an quote/offer by copying the best suited offer of the past: => The parent and all child records are copied (cloned) with the exception of primary keys:
    Furthermore the foreign key of the new child records have to point to the newly created all

    To cope with these situations in the past,I used a table that contains the names of the tables in the database and the last assigned key value.
    New Business objects could get their primary value(s) by making a call to a stored procedure.
    For replication purposes (case 2) a second parameter served as an indicator for the numer of keys to reserve.

    In a VFP App for example, I called that way the remote procedure in the default value (dbc) of the remote view.

    Most databases support an autoincrment or computed value as default value but not a call to a stored procedure.
    I dislike the alternative use of database triggers from my bad experiences in the past (complexity too high, no portability )

    Now I'd like to use the power of the entity framework and still take advantage of using a primary key pool.

    Is there a scenario where this would be posible ?

    Thanks in advance.

     

    Mark Libbrecht

    Thursday, May 7, 2009 8:31 AM

Answers

  • Personally, I just have a simple class to allocate primary key ranges that uses standard ADO.Net to invoke a stored proc with appropriate parameters. I then assign the allocated primary key(s) to new entities.

    In fact, I use this even when the primary key is an identity column to allocate temporary primary keys in order to guarantee uniqueness in the context. The temporary keys are replaced when the changes are persisted.

    There may be more sophisticated ways of doing it, but this works for me.



    Thursday, May 7, 2009 3:29 PM
  • Thanks Graham,


    I'll think I'll go for an extension method GetNewId or something like that to the entity object type that calls a Primary Key Manager (the class you're referring to). The pk manager could derive the table name from the MetaDataWorkspace given the entity type and call the stored procedure in the db. So the complete responsibility for generating pk's would stay in the db (the pk Manager playing a kind of proxy role).


    Mark
    Saturday, May 9, 2009 6:55 AM

All replies

  • Personally, I just have a simple class to allocate primary key ranges that uses standard ADO.Net to invoke a stored proc with appropriate parameters. I then assign the allocated primary key(s) to new entities.

    In fact, I use this even when the primary key is an identity column to allocate temporary primary keys in order to guarantee uniqueness in the context. The temporary keys are replaced when the changes are persisted.

    There may be more sophisticated ways of doing it, but this works for me.



    Thursday, May 7, 2009 3:29 PM
  • Thanks Graham,


    I'll think I'll go for an extension method GetNewId or something like that to the entity object type that calls a Primary Key Manager (the class you're referring to). The pk manager could derive the table name from the MetaDataWorkspace given the entity type and call the stored procedure in the db. So the complete responsibility for generating pk's would stay in the db (the pk Manager playing a kind of proxy role).


    Mark
    Saturday, May 9, 2009 6:55 AM