0

I am using Entity Framework with a Oracle Database for an MVC5 application. For my Controller action where I create a new Vendor entity, I have the below:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> Create([Bind(Include = "Id,vendor_name,created_date,created_by,modified_date,modified_by")] INV_VENDORS iNV_Vendors)
    {
        if (!db.INV_VENDORS.Any(m => m.VENDOR_NAME == iNV_Vendors.VENDOR_NAME))
        {
            //decimal id = db.INV_VENDORS.Last().ID + 1;
            //iNV_Vendors.ID = db.INV_VENDORS.Last().ID + 1;
            iNV_Vendors.CREATED_DATE = DateTime.Now;
            iNV_Vendors.CREATED_BY = System.Environment.UserName;

            ModelState.Clear();
            TryValidateModel(iNV_Vendors);

            if (ModelState.IsValid)
            {

                db.INV_VENDORS.Add(iNV_Vendors);
                await db.SaveChangesAsync();
                return RedirectToAction("Index");


            }
        }
        else
        {
            ModelState.AddModelError("vendor_name", "The Vendor [" + iNV_Vendors.VENDOR_NAME + "] already exists in the table.");
        }
        return View(iNV_Vendors);
    }

When I load the view and enter a value (say, "TEST") the code from breakpoint executes all the way down to await db.SaveChangesAsync(); where it throws the error Oracle.ManagedDataAccess.Client.OracleException ORA-00001: unique constraint (INVENTORY.PK_INV_VENDORS) violated ORA-06512.

I'm not quite sure why I am getting this error, as the database has a Trigger to auto-increment the ID field value each time there is a new INSERT:

CREATE OR REPLACE TRIGGER INV_VENDORS_TRG
BEFORE INSERT
ON INVENTORY.INV_VENDORS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
-- For Toad:  Highlight column ID
  :new.ID := INV_VENDORS_SEQ.nextval;
END INV_VENDORS_TRG;
/

In my INV_VENDORS class of my DAL folder, I have tried specifying the [DatabaseGenerated] option for my ID field, but still receive the same error when trying to create a new VENDOR entity:

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public decimal ID { get; set; }

Through testing it seems that Entity Framework is auto-setting the iNV_Vendors.ID value in my Controller code as 0, which is causing the duplication error -- the table already contains values with ID 1-23 currently.

Does anyone know how to query the database via linq for the last ID value in the table so that I can specify iNV_Vendors.ID as the returned value + 1? (Or perhaps a correct way around this should this be a bad idea?)

Analytic Lunatic
  • 3,853
  • 22
  • 78
  • 120
  • 1
    Entity framework isn't setting the value to 0. Decimal is a value type so it is 0 by default. – Dusty Lau Apr 02 '15 at 21:36
  • I see. I'm still not sure why that field is even defined as `Decimal`. I used the Entity Framework Designer to select my tables and auto-generate the `DAL` classes which made the `int ID` field come across as `decimal` in the class. – Analytic Lunatic Apr 02 '15 at 21:39
  • And you're sure that `ID` is your only PK, i.e. not a compound key with `vendor_name`? – DrewJordan Apr 06 '15 at 14:51
  • wait, you mentioned using the designer to create your classes... is this code-first or database-first? I don't think you can use the `DatabaseGenerated` attribute with database-first... you'd have to do something like this: http://stackoverflow.com/questions/7351260/oracle-entity-in-vs-entity-framework-doesnt-update-the-primary-key-in-code – DrewJordan Apr 06 '15 at 15:31

1 Answers1

0

Getting last id via EF and setting it manually is a very bad idea, as it will certainly fail on concurrent requests.

As Oracle doesn't support autoincrement, you should use trigger workaround as mentioned in this thread (it actually differs a bit from yours current implementation).

Have you tested insert directly on database, without specifiying Id column value?

Community
  • 1
  • 1
Yura
  • 2,013
  • 19
  • 25
  • I have tested manual insertion into the database and confirmed that I do not need to specify the `ID` column value when using TOAD for Oracle. I took a look at your link but I'm not quite seeing what's different compared to the trigger I already have in place? – Analytic Lunatic Apr 02 '15 at 21:42
  • yep, my bad, they are same. Have you tried to profile the actual query EF is poducing? – Yura Apr 02 '15 at 21:56
  • I'm not sure about the actual query, but `iNV_Vendors` that get's added just before `await db.SaveChangesAsync()` has the following: `CREATED_BY: {Me} | CREATED_DATE: {4/3/2015 8:43:57 AM} | ID: 0 | MODIFIED_BY: null | MODIFIED_DATE: null | VENDOR_NAME: "TEST"`? – Analytic Lunatic Apr 03 '15 at 13:48
  • That does look right. Check actual query using profiler, it will help a lot. – Yura Apr 03 '15 at 13:50
  • What is/how do I use Profiler? – Analytic Lunatic Apr 03 '15 at 15:03
  • By profiler I mean any tool, that lets you examine actual SQL queries, that are hitting your database. Don't know much about Oracle, maybe this one should work - [link](http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index-097090.html). For SQL Server I use build-in profiler. So if you get idea, just find profiler that works for you, and look at actual SQL that EF if generating in your situation. – Yura Apr 03 '15 at 15:24
  • Hmm... I'm still not sure how to view the actual SQL Statement (did find a post saying to use Intellitrace Window), but stepping through with Intellitrace I did see where (moving from `.Add(iNV_Vendors)` to `db.SaveChangesAsync();` the following was thrown:`Exception:Caught: "Input string was not in a correct format." (System.FormatException) A System.FormatException was caught: "Input string was not in a correct format." Time: 4/3/2015 10:36:54 AM Thread:Worker Thread[13620]` – Analytic Lunatic Apr 03 '15 at 15:39
  • Intellitrace Window can be used to debug `your` application code. But to look at the SQL statement, you will need external tool, specific for Oracle SQL, not your application. – Yura Apr 03 '15 at 16:04