Problem in getting inserted record`s ID Using transaction with Oracle.ManagedDataAccess.EntityFramework










0















In a .Net framework project I added a EF 6.x DbContext Generator using visual studio that adds an .edmx file with some .tt files in it; that implements my database first structure.



Then I connected to my oracle database and added my tables into it with some models like this:



// DatabaseContext.tt > USER_TYPE.cs
public class USER_TYPE

public int ID get; set; // have sequence and insert trigger.
public string NAME get; set;


// DatabaseContext.tt > USER.cs
public class USER

public int ID get; set; // have sequence and insert trigger.
public int USER_TYPE_ID get; set; // foreign key to USER_TYPES.ID
public string NAME get; set;



Also Oracle.ManagedDataAccess.EntityFramework Nuget package is installed.



Both tables have ID as primary key with trigger and sequence that will give the ID column, a unique value.



Now I want to begin a transaction to insert some records in the tables that are related together:



using (var db = new DatabaseContext())
using (var transaction = db.Database.BeginTransaction())

// insert userType
var userType = new USER_TYPE

NAME = "admin"
;

db.USER_TYPES.Add(userType);
db.SaveChanges();

// userType.ID == 0 : true

// insert user
var user = new USER

NAME = "admin user",
USER_TYPE_ID = userType.ID
;

db.USERS.Add(user);
db.SaveChanges();

transaction.Commit();



The problem is after db.USER_TYPES.Add(userType); db.SaveChanges() we know the userType.ID will remains 0 (because we are using oracle).



There is some solutions in the web that suggest to get the latest inserted ID by executing a select max ID query or get next sequence value before insert. but when we use Transaction, there is no actual record in db to select max, or selecting from the sequence is not accurate and is vague.



Any help will be appreciated.










share|improve this question

















  • 1





    You need to configure your EF model to tell it that the database is generating that value, and then it will query it after insert.

    – David Browne - Microsoft
    Nov 14 '18 at 17:44















0















In a .Net framework project I added a EF 6.x DbContext Generator using visual studio that adds an .edmx file with some .tt files in it; that implements my database first structure.



Then I connected to my oracle database and added my tables into it with some models like this:



// DatabaseContext.tt > USER_TYPE.cs
public class USER_TYPE

public int ID get; set; // have sequence and insert trigger.
public string NAME get; set;


// DatabaseContext.tt > USER.cs
public class USER

public int ID get; set; // have sequence and insert trigger.
public int USER_TYPE_ID get; set; // foreign key to USER_TYPES.ID
public string NAME get; set;



Also Oracle.ManagedDataAccess.EntityFramework Nuget package is installed.



Both tables have ID as primary key with trigger and sequence that will give the ID column, a unique value.



Now I want to begin a transaction to insert some records in the tables that are related together:



using (var db = new DatabaseContext())
using (var transaction = db.Database.BeginTransaction())

// insert userType
var userType = new USER_TYPE

NAME = "admin"
;

db.USER_TYPES.Add(userType);
db.SaveChanges();

// userType.ID == 0 : true

// insert user
var user = new USER

NAME = "admin user",
USER_TYPE_ID = userType.ID
;

db.USERS.Add(user);
db.SaveChanges();

transaction.Commit();



The problem is after db.USER_TYPES.Add(userType); db.SaveChanges() we know the userType.ID will remains 0 (because we are using oracle).



There is some solutions in the web that suggest to get the latest inserted ID by executing a select max ID query or get next sequence value before insert. but when we use Transaction, there is no actual record in db to select max, or selecting from the sequence is not accurate and is vague.



Any help will be appreciated.










share|improve this question

















  • 1





    You need to configure your EF model to tell it that the database is generating that value, and then it will query it after insert.

    – David Browne - Microsoft
    Nov 14 '18 at 17:44













0












0








0








In a .Net framework project I added a EF 6.x DbContext Generator using visual studio that adds an .edmx file with some .tt files in it; that implements my database first structure.



Then I connected to my oracle database and added my tables into it with some models like this:



// DatabaseContext.tt > USER_TYPE.cs
public class USER_TYPE

public int ID get; set; // have sequence and insert trigger.
public string NAME get; set;


// DatabaseContext.tt > USER.cs
public class USER

public int ID get; set; // have sequence and insert trigger.
public int USER_TYPE_ID get; set; // foreign key to USER_TYPES.ID
public string NAME get; set;



Also Oracle.ManagedDataAccess.EntityFramework Nuget package is installed.



Both tables have ID as primary key with trigger and sequence that will give the ID column, a unique value.



Now I want to begin a transaction to insert some records in the tables that are related together:



using (var db = new DatabaseContext())
using (var transaction = db.Database.BeginTransaction())

// insert userType
var userType = new USER_TYPE

NAME = "admin"
;

db.USER_TYPES.Add(userType);
db.SaveChanges();

// userType.ID == 0 : true

// insert user
var user = new USER

NAME = "admin user",
USER_TYPE_ID = userType.ID
;

db.USERS.Add(user);
db.SaveChanges();

transaction.Commit();



The problem is after db.USER_TYPES.Add(userType); db.SaveChanges() we know the userType.ID will remains 0 (because we are using oracle).



There is some solutions in the web that suggest to get the latest inserted ID by executing a select max ID query or get next sequence value before insert. but when we use Transaction, there is no actual record in db to select max, or selecting from the sequence is not accurate and is vague.



Any help will be appreciated.










share|improve this question














In a .Net framework project I added a EF 6.x DbContext Generator using visual studio that adds an .edmx file with some .tt files in it; that implements my database first structure.



Then I connected to my oracle database and added my tables into it with some models like this:



// DatabaseContext.tt > USER_TYPE.cs
public class USER_TYPE

public int ID get; set; // have sequence and insert trigger.
public string NAME get; set;


// DatabaseContext.tt > USER.cs
public class USER

public int ID get; set; // have sequence and insert trigger.
public int USER_TYPE_ID get; set; // foreign key to USER_TYPES.ID
public string NAME get; set;



Also Oracle.ManagedDataAccess.EntityFramework Nuget package is installed.



Both tables have ID as primary key with trigger and sequence that will give the ID column, a unique value.



Now I want to begin a transaction to insert some records in the tables that are related together:



using (var db = new DatabaseContext())
using (var transaction = db.Database.BeginTransaction())

// insert userType
var userType = new USER_TYPE

NAME = "admin"
;

db.USER_TYPES.Add(userType);
db.SaveChanges();

// userType.ID == 0 : true

// insert user
var user = new USER

NAME = "admin user",
USER_TYPE_ID = userType.ID
;

db.USERS.Add(user);
db.SaveChanges();

transaction.Commit();



The problem is after db.USER_TYPES.Add(userType); db.SaveChanges() we know the userType.ID will remains 0 (because we are using oracle).



There is some solutions in the web that suggest to get the latest inserted ID by executing a select max ID query or get next sequence value before insert. but when we use Transaction, there is no actual record in db to select max, or selecting from the sequence is not accurate and is vague.



Any help will be appreciated.







c# oracle entity-framework entity-framework-6 oracle-manageddataaccess






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 14 '18 at 17:08









BagheraniBagherani

1,2171430




1,2171430







  • 1





    You need to configure your EF model to tell it that the database is generating that value, and then it will query it after insert.

    – David Browne - Microsoft
    Nov 14 '18 at 17:44












  • 1





    You need to configure your EF model to tell it that the database is generating that value, and then it will query it after insert.

    – David Browne - Microsoft
    Nov 14 '18 at 17:44







1




1





You need to configure your EF model to tell it that the database is generating that value, and then it will query it after insert.

– David Browne - Microsoft
Nov 14 '18 at 17:44





You need to configure your EF model to tell it that the database is generating that value, and then it will query it after insert.

– David Browne - Microsoft
Nov 14 '18 at 17:44












1 Answer
1






active

oldest

votes


















0














Thanks to David Browne's comment in the first post and using this Nuget package, I simply changed the StoredGeneratedPattern to Identity of my primary key column in my .edmx file.



By this change, the Entity Framework will not pass the ID in the insert sql script to the database:



insert into MY_TABLE (NAME) VALUES (@some_value) -- ID is not passed.


And also after insertion, the ID (I mean primary key column) will be obtained automatically from the database, even if I use transactions.



using (var db = new DatabaseContext())
using (var transaction = db.Database.BeginTransaction())

var userType = new USER_TYPE

NAME = "admin"
;

db.USER_TYPES.Add(userType);
db.SaveChanges();

Debug.Assert.IsTrue(userType.ID != 0); // ✔️ will be passed.






share|improve this answer






















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305434%2fproblem-in-getting-inserted-records-id-using-transaction-with-oracle-manageddat%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    Thanks to David Browne's comment in the first post and using this Nuget package, I simply changed the StoredGeneratedPattern to Identity of my primary key column in my .edmx file.



    By this change, the Entity Framework will not pass the ID in the insert sql script to the database:



    insert into MY_TABLE (NAME) VALUES (@some_value) -- ID is not passed.


    And also after insertion, the ID (I mean primary key column) will be obtained automatically from the database, even if I use transactions.



    using (var db = new DatabaseContext())
    using (var transaction = db.Database.BeginTransaction())

    var userType = new USER_TYPE

    NAME = "admin"
    ;

    db.USER_TYPES.Add(userType);
    db.SaveChanges();

    Debug.Assert.IsTrue(userType.ID != 0); // ✔️ will be passed.






    share|improve this answer



























      0














      Thanks to David Browne's comment in the first post and using this Nuget package, I simply changed the StoredGeneratedPattern to Identity of my primary key column in my .edmx file.



      By this change, the Entity Framework will not pass the ID in the insert sql script to the database:



      insert into MY_TABLE (NAME) VALUES (@some_value) -- ID is not passed.


      And also after insertion, the ID (I mean primary key column) will be obtained automatically from the database, even if I use transactions.



      using (var db = new DatabaseContext())
      using (var transaction = db.Database.BeginTransaction())

      var userType = new USER_TYPE

      NAME = "admin"
      ;

      db.USER_TYPES.Add(userType);
      db.SaveChanges();

      Debug.Assert.IsTrue(userType.ID != 0); // ✔️ will be passed.






      share|improve this answer

























        0












        0








        0







        Thanks to David Browne's comment in the first post and using this Nuget package, I simply changed the StoredGeneratedPattern to Identity of my primary key column in my .edmx file.



        By this change, the Entity Framework will not pass the ID in the insert sql script to the database:



        insert into MY_TABLE (NAME) VALUES (@some_value) -- ID is not passed.


        And also after insertion, the ID (I mean primary key column) will be obtained automatically from the database, even if I use transactions.



        using (var db = new DatabaseContext())
        using (var transaction = db.Database.BeginTransaction())

        var userType = new USER_TYPE

        NAME = "admin"
        ;

        db.USER_TYPES.Add(userType);
        db.SaveChanges();

        Debug.Assert.IsTrue(userType.ID != 0); // ✔️ will be passed.






        share|improve this answer













        Thanks to David Browne's comment in the first post and using this Nuget package, I simply changed the StoredGeneratedPattern to Identity of my primary key column in my .edmx file.



        By this change, the Entity Framework will not pass the ID in the insert sql script to the database:



        insert into MY_TABLE (NAME) VALUES (@some_value) -- ID is not passed.


        And also after insertion, the ID (I mean primary key column) will be obtained automatically from the database, even if I use transactions.



        using (var db = new DatabaseContext())
        using (var transaction = db.Database.BeginTransaction())

        var userType = new USER_TYPE

        NAME = "admin"
        ;

        db.USER_TYPES.Add(userType);
        db.SaveChanges();

        Debug.Assert.IsTrue(userType.ID != 0); // ✔️ will be passed.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 17 '18 at 7:30









        BagheraniBagherani

        1,2171430




        1,2171430





























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53305434%2fproblem-in-getting-inserted-records-id-using-transaction-with-oracle-manageddat%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            How to read a connectionString WITH PROVIDER in .NET Core?

            Museum of Modern and Contemporary Art of Trento and Rovereto

            In R, how to develop a multiplot heatmap.2 figure showing key labels successfully