none
ADO .NET Entity framework and tables with autogenerated column RRS feed

  • Question

  • Hi,

    I am using the ADO .NET Entity model in my application. The backend is SqlLite

    To simplify me question, assume that i have two tables tableA and tableB.

    tableA has 2 columns , idA and description

    tableB  has few columns, idB and description and other columns

    idA column of tableA is an autogenerated column (sequence numbering) and is the primary key of the table.

    idB column of tableB refers to the idA column of tableA (Foreign key constraint).

    So in effect, tableA has my master record and tableB has detail records

    Now i want to insert data in both tables using ADO .NET entity model.

    I start with insertion of data in tableA. I give only the value of the 'description' as idA column is auto generated.

    Next i want to add multiple rows with the same id as the one in the above row of tableB.This is where my problem is.

    How do i find out the value of the generated idA for the tableA?

    ----------------------

    Dim entities as New MyEntity
    
    Dim tableAObj as New tableA
    
    Dim tableBObj as New tableB
    
    tableAObj .Description="SomeValue"
    
    ' Add the new row to the entitiy model
    
    entities.tableAObjs.AddObject(tableAObj)
    
    ' persist the changes
    
    entities.SaveChanges()
    
    
    
     The changes are persisted, but now how do i get the autogenerated value back for adding records in tableB ??
    I can have multiple inserts in my application by number of users, so a simple 'Max' of the ids wont help.


    Tuesday, October 11, 2011 11:55 AM

Answers

  • Hi,

    I suposse you are using SQL Server, aren't it?

    In this case, why you don't use an Identity field?

    I'm working with both SQL Server and Oracle databases and I'm solving this problem automatically with EF4 in this way:

    - SQL Server: My keys are defined as Identity

    - Oracle: I use a Sequence and an Insert trigger

    - EF4: I add the following attribute to my key property fields StoreGeneratedPattern="Identity"

    In this way, EF4 save a main entity root and all their related entities automatically with the correct keys in each case.

    Hope this can help you,

    JAReyes.


    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.
    Tuesday, October 11, 2011 1:19 PM
  • On 10/11/2011 7:55 AM, Dhananjay Kher wrote:
     
    >
    > How do i find out the value of the generated idA for the tableA?
    >
    After you insert an EF object into a table based on an identity-key as
    the primary-key, then the new identity-key property for the EF object is
    automatically populated in the object's property after the insert and
    save().
     
    So after the EF entity is inserted and Savechanges() is done, then you
    can address the EF object(still in memory) after the Savechanges() and
    get the key from the EF object for the object just inserted.
     
     
    Tuesday, October 11, 2011 2:15 PM

All replies

  • Hi,

    I am using the ADO .NET Entity model in my application. The backend is SqlLite

    To simplify me question, assume that i have two tables tableA and tableB.

    tableA has 2 columns , idA and description

    tableB  has few columns, idB and description and other columns

    idA column of tableA is an autogenerated column (sequence numbering) and is the primary key of the table.

    idB column of tableB refers to the idA column of tableA (Foreign key constraint).

    So in effect, tableA has my master record and tableB has detail records

    Now i want to insert data in both tables using ADO .NET entity model.

    I start with insertion of data in tableA. I give only the value of the 'description' as idA column is auto generated.

    Next i want to add multiple rows with the same id as the one in the above row of tableB.This is where my problem is.

    How do i find out the value of the generated idA for the tableA?

    ----------------------

    Dim entities as New MyEntity
    
    Dim tableAObj as New tableA
    
    Dim tableBObj as New tableB
    
    tableAObj .Description="SomeValue"
    
    ' Add the new row to the entitiy model
    
    entities.tableAObjs.AddObject(tableAObj)
    
    ' persist the changes
    
    entities.SaveChanges()
    
    
     The changes are persisted, but now how do i get the autogenerated value back for adding records in tableB ??
    I can have multiple inserts in my application by number of users, so a simple 'Max' of the ids wont help.




    • Edited by Dhananjay Kher Tuesday, October 11, 2011 10:37 AM
    • Merged by Paul Zhou Thursday, October 13, 2011 3:31 AM the same question
    Tuesday, October 11, 2011 10:34 AM
  • try posting your question in http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads
    Mark Answered, if it solves your question
    Rohit Arora
    Tuesday, October 11, 2011 11:31 AM
  • try posting your question in http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads
    Mark Answered, if it solves your question
    Rohit Arora

    Thanks. Have reposted the question in the said forum
    Tuesday, October 11, 2011 11:56 AM
  • Hi,

    I suposse you are using SQL Server, aren't it?

    In this case, why you don't use an Identity field?

    I'm working with both SQL Server and Oracle databases and I'm solving this problem automatically with EF4 in this way:

    - SQL Server: My keys are defined as Identity

    - Oracle: I use a Sequence and an Insert trigger

    - EF4: I add the following attribute to my key property fields StoreGeneratedPattern="Identity"

    In this way, EF4 save a main entity root and all their related entities automatically with the correct keys in each case.

    Hope this can help you,

    JAReyes.


    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.
    Tuesday, October 11, 2011 1:19 PM
  • On 10/11/2011 7:55 AM, Dhananjay Kher wrote:
     
    >
    > How do i find out the value of the generated idA for the tableA?
    >
    After you insert an EF object into a table based on an identity-key as
    the primary-key, then the new identity-key property for the EF object is
    automatically populated in the object's property after the insert and
    save().
     
    So after the EF entity is inserted and Savechanges() is done, then you
    can address the EF object(still in memory) after the Savechanges() and
    get the key from the EF object for the object just inserted.
     
     
    Tuesday, October 11, 2011 2:15 PM