none
Identity insert throws: Invalid Operation Exception The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type. RRS feed

  • Question

  • Forgive me if this has been asked before, but I am fairly new to LINQ to SQL and I haven't found a similar question in the forums.

     

    I am trying to insert a new row into my database using the following code:

                    invoicePage = new InvoicePage();

                    invoicePage.Visible = true;

                    invoicePage.InvoiceLineCostTotal = 0;

                    _db.InvoicePages.InsertOnSubmit(invoicePage);

                    customerBillingCode.InvoicePageType.InvoicePages.Add(invoicePage);

                    invoice.InvoicePages.Add(invoicePage);

    When I call _db.SubmitChanges(); it throws an Invalid Operation Exception.  After trying a few different things, I eventually flipped the Nullable property to TRUE on InvoicePage.InvoicePageID attribute that is mapped to the IDENTITY column.

    Running my code again allows _db.SubmitChanges() to execute successfully, but the invoicePage.InvoicePageID property has been set to null, not to the value saved in the database.

    The .dbml file for my schema was autogenerated by dragging and dropping the table from the server explorer onto the design surface.  The InvoicePageID field seems to have all the properties set as I expect them:

    Type: int (System.Int32)

    Auto Generated Value: True

    Auto-sync: OnInsert

    Nullable: false

    Primary Key: true

    Read Only: false

    Server Data Type: Int NOT NULL IDENTITY

    Source: InvoicePageID

     

    Can anyone answer why this insert is failing to properly retrieve the IDENTITY value on insert and instead is assigning NULL?

    P.S. All of my tables have AFTER INSERT, UPDATE triggers on them, but disabling the triggers has no effect on this issue.

    P.P.S. I have another table, Invoice that is configured similarly to InvoicePage and it works just fine.

     

    Thanks in advance for your assistance,

    Paul Chavez

    Friday, September 17, 2010 4:51 PM

Answers

  • DOH! My bad..

    A little more information, I hope it helps.

    I ran a SQL Trace to see what LINQ is doing in the database, the insert generates the following statement:

     

    exec sp_executesql N'INSERT INTO [dbo].[InvoicePage]([InvoiceID], [InvoicePageTypeID], [Visible], [InvoiceLineCostTotal])

    VALUES (@p0, @p1, @p2, @p3)

     

    SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 int,@p1 int,@p2 bit,@p3 decimal(10,2)',@p0=1093,@p1=4,@p2=1,@p3=0

    The results of the SELECT returns a single NULL.

    I tried running the statement through Management Studio with the same results.

    I took another look at the Table Triggers and discovered a bug that was preventing the record from being inserted.

     

    Long story short, LINQ was behaving correctly.

    Sorry to inconvenience you.

    Paul

     

    Friday, September 17, 2010 7:16 PM