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

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.

Answer 1

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.
}