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

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

Accepted Answer

Change your query to use DUAL instead 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