MVC EF Oracle: How do I use a sequence to generate the next ID on insert?

asp.net-mvc asp.net-mvc-5 entity-framework entity-framework-6 oracle

Question

using Oracle, MVC 5, EF 6, and the database first. I'm attempting to have an object named Mission's ID generated automatically. Currently, a stored procedure in the database uses the insert command and the sequence MISSION SEQ to do this. Since I'm having difficulties getting this stored procedure to function, I decided to try handling it myself as follows:

[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);
}

Error message: "Sequence not permitted here." Because NEXTVAL can only be used in an insert statement, I suppose. I believe I could just run a query to collect the existing MISSION IDs and add 1 to them to construct the next one, but how can I make sure that if many people create something, they all get a different number? Also, would that MISSION ID be lost if the Create fails? Any assistance to lead me in the correct way would be much appreciated. Thanks.

1
0
7/13/2017 3:52:45 PM

Accepted Answer

DUAL should be used in your query in place of the sequence name:

string query = "select MISSION_SEQ.NEXTVAL from DUAL";

0
7/13/2017 3:57:39 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow