Problem in getting inserted record`s ID Using transaction with Oracle.ManagedDataAccess.EntityFramework
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
add a comment |
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
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
add a comment |
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
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
c# oracle entity-framework entity-framework-6 oracle-manageddataaccess
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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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.
add a comment |
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
add a comment |
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.
add a comment |
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.
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.
answered Nov 17 '18 at 7:30
BagheraniBagherani
1,2171430
1,2171430
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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