Using MVC 5, EF 6, Oracle, and database first. I'm trying to auto-generate the ID of an object called Mission. Currently in the database there is a stored procedure which does this by using a sequence, MISSION_SEQ, on the insert command. I'm having trouble getting this stored procedure to work, so I thought I could try to handle it myself like this:
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Create([Bind(Include = "<a bunch of fields, but I removed the MISSION_ID field")] MISSION mission)
{
if (ModelState.IsValid)
{
string query = "select MISSION_SEQ.NEXTVAL from MISSION_SEQ";
var query_result = db.Database.SqlQuery<decimal>(query); //throws error "sequence not allowed here"
decimal mission_id = query_result.First();
mission.MISSION_ID = (decimal)mission_id;
db.MISSION.Add(mission);
await db.SaveChangesAsync();
return RedirectToAction("Index");
}
return View(mission);
}
This throws an error "Sequence not allowed here" I guess because you can only use NEXTVAL in an insert statement. I think I could just query the current MISSION_IDs and generate the next one by adding 1 to it, but how can I ensure that if multiple users are doing a create, they each get a unique number? Also if the Create fails, will that MISSION_ID be lost? I'm looking for any help to point me in the right direction here. Thanks.
Change your query to use DUAL instead of the sequence name:
string query = "select MISSION_SEQ.NEXTVAL from DUAL";