Saving Data - entity framework SQL Insert statement creates a null value for a field with a value.

[NeilMJohnson]

Describe what is not working as expected.

I have an application that is posting payments. It writes new records to two tables and updates a third. When saving the changes, the generated insert statements work fine for several hundred inserts and then changes one of the parameters to a null. This then violates the database integrity which throws an error.

Turning on logging shows the last few inserts, the second to last insert to the Vouchers table shows that the second parameter, the BORROWER_ID has a value. The Last insert has a null where the Borrower_ID parameter should be:

INSERT [dbo].[VOUCHERS]([BATCH_NUM], [BORROWER_ID], [InvestorID], [MICInvestorID], [ReceiptTypeValue], [PAYMENT_DATE], [PaymentMethod], [PMT_METHOD], [PMT_AMT], [RENEWAL_FEE], [CHG_AMT], [TX_TypeID], [FromTrustAmount], [USER_ID], [Notes], [Change], [X1], [X2], [X5], [X10], [X20], [X50], [X100], [CashSubtotal], [REFERENCE], [ChequeAmount], [DraftNumber], [DraftAmount], [TX_ID]) VALUES (@0, @1, NULL, NULL, @2, @3, NULL, NULL, @4, @5, @6, NULL, @7, NULL, NULL, @8, @9, @10, @11, @12, @13, @14, @15, @16, NULL, @17, NULL, @18, NULL) SELECT [VOUCHER_NUM], [ReceiptType], [Version], [RowGUID] FROM [dbo].[VOUCHERS] WHERE @@ROWCOUNT > 0 AND [VOUCHER_NUM] = scope_identity() -- @0: '12680' (Type = Int32) -- @1: '2224' (Type = Int32) -- @2: '1' (Type = Byte, Size = 1) -- @3: '01/06/2019 12:00:00 AM' (Type = DateTime2) -- @4: '1396.2400' (Type = Decimal, Precision = 19, Scale = 4) -- @5: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @6: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @7: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @8: '0' (Type = Decimal, Precision = 10, Scale = 4) -- @9: '0' (Type = Int16) -- @10: '0' (Type = Int16) -- @11: '0' (Type = Int16) -- @12: '0' (Type = Int16) -- @13: '0' (Type = Int16) -- @14: '0' (Type = Int16) -- @15: '0' (Type = Int16) -- @16: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @17: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @18: '0' (Type = Decimal, Precision = 19, Scale = 4) -- Executing at 06/09/2019 1:30:10 PM -07:00 -- Completed in 8 ms with result: SqlDataReader

UPDATE [dbo].[Payments] SET [PaymentPostingStatus] = @0, [VoucherID] = @1 WHERE (([PADID] = @2) AND ([Version] = @3)) SELECT [RowGUID], [Version] FROM [dbo].[Payments] WHERE @@ROWCOUNT > 0 AND [PADID] = @2 -- @0: '1' (Type = Byte, Size = 1) -- @1: '30856' (Type = Int32) -- @2: '2004' (Type = Int32) -- @3: 'System.Byte[]' (Type = Binary, Size = 8) -- Executing at 06/09/2019 1:30:10 PM -07:00 -- Completed in 8 ms with result: SqlDataReader

INSERT [dbo].[VOUCHERS]([BATCH_NUM], [BORROWER_ID], [InvestorID], [MICInvestorID], [ReceiptTypeValue], [PAYMENT_DATE], [PaymentMethod], [PMT_METHOD], [PMT_AMT], [RENEWAL_FEE], [CHG_AMT], [TX_TypeID], [FromTrustAmount], [USER_ID], [Notes], [Change], [X1], [X2], [X5], [X10], [X20], [X50], [X100], [CashSubtotal], [REFERENCE], [ChequeAmount], [DraftNumber], [DraftAmount], [TX_ID]) VALUES (@0, NULL, NULL, NULL, @1, @2, NULL, NULL, @3, @4, @5, NULL, @6, NULL, NULL, @7, @8, @9, @10, @11, @12, @13, @14, @15, NULL, @16, NULL, @17, NULL) SELECT [VOUCHER_NUM], [ReceiptType], [Version], [RowGUID] FROM [dbo].[VOUCHERS] WHERE @@ROWCOUNT > 0 AND [VOUCHER_NUM] = scope_identity() -- @0: '12680' (Type = Int32) -- @1: '1' (Type = Byte, Size = 1) -- @2: '01/06/2019 12:00:00 AM' (Type = DateTime2) -- @3: '2329.9200' (Type = Decimal, Precision = 19, Scale = 4) -- @4: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @5: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @6: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @7: '0' (Type = Decimal, Precision = 10, Scale = 4) -- @8: '0' (Type = Int16) -- @9: '0' (Type = Int16) -- @10: '0' (Type = Int16) -- @11: '0' (Type = Int16) -- @12: '0' (Type = Int16) -- @13: '0' (Type = Int16) -- @14: '0' (Type = Int16) -- @15: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @16: '0' (Type = Decimal, Precision = 19, Scale = 4) -- @17: '0' (Type = Decimal, Precision = 19, Scale = 4) -- Executing at 06/09/2019 1:30:10 PM -07:00 A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll -- Failed in 122 ms with error: The INSERT statement conflicted with the CHECK constraint "CK_VOUCHERS". The conflict occurred in database "ArmadaProduction", table "dbo.VOUCHERS". The statement has been terminated.

CK_Vouchers is checking to ensure that either the Borrower_ID has a value or the InvestorID has a value. Of the 160+ records being written, this is close to the end. I check the values to be written to see if any have a null Borrower_ID and all of them have values.

This fragment of the code that creates a Voucher is:

Steps to reproduce

   Private Function createVoucher(batch As Batch, pmtVM As PaymentViewModel, Amount As Decimal) As Voucher
        Dim vchr As Voucher = DBCTX.DatabaseContext.Vouchers.Create()
        With vchr
            .BorrowerID = pmtVM.BorrowerID
            '.Borrower = pmtVM.Borrower
            .Batch = batch
            .PaymentDate = pmtVM.PaymentDate
            .Amount = Amount
            .ReceiptType = CommonResources.Receipts.ReceiptTypes.Payment
            .UserID = Me.CurrentUserID
            If pmtVM.TakeFromTrustAmount.HasValue Then .FromTrustAmount = pmtVM.TakeFromTrustAmount.Value
            .PaymentMethod = pmtVM.PaymentMethod
            If .PaymentMethod.HasValue Then
                .PaymentMethodDescription = EnumFunctions.GetEnumName(Of PaymentMethods)(.PaymentMethod.Value)
            End If
            .Reference = pmtVM.ReferenceNumber
        End With
        batch.Vouchers.Add(vchr)
        DBCTX.DatabaseContext.Vouchers.Add(vchr)
        Return vchr
    End Function

. . . Dim missingBorrowers As List(Of Voucher) = pmtsTOProcess.Where(Function(p) Not p.PaymentEntity.Voucher.BorrowerID.HasValue).Select(Function(p) p.PaymentEntity.Voucher).ToList RecordSaved = DBCTX.SaveChanges()

The missingBorrowers list returns 0 entries. The BorrowerID in the PMTVM is defined as an Integer and is non-nullable. DBCTX is a synchronized wrapper for the database context called ArmadaDBEntities which is generated from the Entity Framework model. Scanning the records to be updated shows that the records in question all have a valid value for the BorrowerID.

Schema Definition for Vouchers table: CREATE TABLE [dbo].[VOUCHERS]( [VOUCHER_NUM] [int] IDENTITY(1,1) NOT NULL, [BATCH_NUM] [int] NULL, [BORROWER_ID] [int] NULL, [InvestorID] [int] NULL, [MICInvestorID] [int] NULL, [ReceiptTypeValue] [tinyint] NOT NULL, [ReceiptType] AS ([dbo].GetReceiptType), [PAYMENT_DATE] [datetime] NOT NULL, [PaymentMethod] [tinyint] NULL, [PMT_METHOD] nvarchar NULL, [PMT_AMT] [money] NOT NULL CONSTRAINT [DFVOUCHERSPMT_AM2180FB33] DEFAULT ((0)), [RENEWAL_FEE] [money] NOT NULL CONSTRAINT [DFVOUCHERSRENEWA__22751F6C] DEFAULT ((0)), [CHG_AMT] [money] NOT NULL CONSTRAINT [DFVOUCHERS__CHG_AM__236943A5] DEFAULT ((0)), [TX_TypeID] [int] NULL, [FromTrustAmount] [money] NOT NULL CONSTRAINT [DF_VOUCHERS_FromTrustAmount] DEFAULT ((0)), [USER_ID] [int] NULL, [Notes] nvarchar NULL, [Change] [smallmoney] NOT NULL CONSTRAINT [DF_VOUCHERS_Change] DEFAULT ((0)), [X1] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X1] DEFAULT ((0)), [X2] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X2] DEFAULT ((0)), [X5] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X5] DEFAULT ((0)), [X10] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X10] DEFAULT ((0)), [X20] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X20] DEFAULT ((0)), [X50] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X50] DEFAULT ((0)), [X100] [smallint] NOT NULL CONSTRAINT [DF_VOUCHERS_X100] DEFAULT ((0)), [CashSubtotal] [money] NOT NULL CONSTRAINT [DF_VOUCHERS_CashSubtotal] DEFAULT ((0)), [REFERENCE] nvarchar NULL, [ChequeAmount] [money] NOT NULL CONSTRAINT [DF_VOUCHERS_ChequeAmount] DEFAULT ((0)), [DraftNumber] nvarchar NULL, [DraftAmount] [money] NOT NULL CONSTRAINT [DF_VOUCHERS_DraftAmount] DEFAULT ((0)), [TX_ID] [int] NULL, [Version] [timestamp] NOT NULL, [RowGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_VOUCHERS_RowGUID] DEFAULT (newsequentialid()), CONSTRAINT [PK_Vouchers] PRIMARY KEY NONCLUSTERED ( [VOUCHER_NUM] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO

IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_VOUCHERS]') AND parent_object_id = OBJECT_ID(N'[dbo].[VOUCHERS]')) ALTER TABLE [dbo].[VOUCHERS] WITH NOCHECK ADD CONSTRAINT [CK_VOUCHERS] CHECK ((NOT [BORROWER_ID] IS NULL OR NOT [InvestorID] IS NULL)) GO

IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_VOUCHERS]') AND parent_object_id = OBJECT_ID(N'[dbo].[VOUCHERS]')) ALTER TABLE [dbo].[VOUCHERS] CHECK CONSTRAINT [CK_VOUCHERS] GO

Further technical details

EF version: 6.2 Database Provider: EntityFramework.SqlServer Operating system: Windows 10) IDE: Visual Studio 2010.40219.1 SP1rel

[ajcvickers]

@NeilMJohnson There isn't enough information here to reproduce the issue without making a lot of guesses. Could you please post a complete and runnable, but minimal, project/solution that reproduces the behavior you are seeing.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.