dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Cloud Applications
Post Reply
danielrail
Posts: 11
Joined: Tue 10 Feb 2015 18:22

dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by danielrail » Mon 08 May 2017 16:50

Hi,

I'm using C# in Visual Studio 2015, targeting .Net Framework 4.6, using EF 6.1.3, and dotConnect for QuickBooks 1.6.378.

I get the following error:
Exception thrown: 'System.Data.Entity.Core.UpdateException' in EntityFramework.dll
A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'JournalEntryId'.
The error occurs on the QBOEntities.SaveChanges() line, in the following code. Also QBOEntities is defined as "public partial class QuickBooksEntities : ObjectContext".

Code: Select all

                    vJournalEntry = QBOEntities.JournalEntries.CreateObject();
                    QBOEntities.JournalEntries.AddObject(vJournalEntry);
                    vJournalEntry.TxnDate = vInvoice.INVOICEDATE;
                    vJournalEntry.DocNumber = $"FI{vInvoice.INVOICE_NO}";
                    vJournalEntry.PrivateNote = vInvoiceMemo;
                    if (!string.IsNullOrWhiteSpace(vInvoice.TAXE1_SHORTDESC))
                    {
                        vTaxCodeID = GetTaxForShortDesc(vInvoice.TAXE1_SHORTDESC)?.QBO_ACCOUNTING_ID;
                    }
                    else
                    {
                        vTaxCodeID = string.Empty;
                    }
                    vClassID = vInvoice.OfficeDepartment?.QBO_ACCOUNT_ID;
                    if (string.IsNullOrWhiteSpace(vClassID))
                    {
                        vClassID = vInvoice.OfficeMaster?.QBO_ACCOUNT_ID;
                    }

                    #region Invoice Header
                    vJournalEntryLineItem = QBOEntities.JournalEntryLineItems.CreateObject();
                    //QBOEntities.JournalEntryLineItems.AddObject(vJournalEntryLineItem);
                    vJournalEntryLineItem.Amount = Math.Abs(vInvoice.PATIENT_SUBTOTAL.GetValueOrDefault(0));
                    if (!string.IsNullOrWhiteSpace(vTaxCodeID))
                    {
                        vJournalEntryLineItem.JournalEntryLineDetailTaxCodeRefId = vTaxCodeID;
                        vJournalEntryLineItem.JournalEntryLineDetailTaxApplicableOn = FiloptoQBOConstants.QBOSales;
                        vJournalEntryLineItem.JournalEntryLineDetailTaxAmount = Math.Abs(vInvoice.TAXE1_AMOUNT.GetValueOrDefault(0)) + Math.Abs(vInvoice.TAXE2_AMOUNT.GetValueOrDefault(0)) + Math.Abs(vInvoice.TAXE3_AMOUNT.GetValueOrDefault(0));
                    }
                    if (vInvoice.PATIENT_SUBTOTAL.GetValueOrDefault(0) > 0)
                    {
                        vJournalEntryLineItem.JournalEntryLineDetailPostingType = FiloptoQBOConstants.QBODebit;
                    }
                    else
                    {
                        vJournalEntryLineItem.JournalEntryLineDetailPostingType = FiloptoQBOConstants.QBOCredit;
                    }
                    vJournalEntryLineItem.JournalEntryLineDetailAccountRefId = QBOSettings.QBOnlineSettings.AR_ACCOUNT_ID;
                    vJournalEntryLineItem.Description = vInvoiceMemo;
                    vJournalEntryLineItem.JournalEntryLineDetailClassRefId = vClassID;
                    vJournalEntry.JournalEntryLineItems.Add(vJournalEntryLineItem);
                    #endregion

                    #region Invoice Lines
                    foreach (INVOICEDETAIL vInvoiceLineItem in vInvoice.InvoiceDetails)
                    {
                        vJournalEntryLineItem = QBOEntities.JournalEntryLineItems.CreateObject();
                        //QBOEntities.JournalEntryLineItems.AddObject(vJournalEntryLineItem);
                        vJournalEntryLineItem.Amount = Math.Abs(vInvoiceLineItem.PATIENT_SUBTOTAL.GetValueOrDefault(0));
                        if (!string.IsNullOrWhiteSpace(vTaxCodeID))
                        {
                            vJournalEntryLineItem.JournalEntryLineDetailTaxCodeRefId = vTaxCodeID;
                            vJournalEntryLineItem.JournalEntryLineDetailTaxApplicableOn = FiloptoQBOConstants.QBOSales;
                            vJournalEntryLineItem.JournalEntryLineDetailTaxAmount = Math.Abs(vInvoiceLineItem.TAXE_1_AMOUNT.GetValueOrDefault(0)) + Math.Abs(vInvoiceLineItem.TAXE_2_AMOUNT.GetValueOrDefault(0)) + Math.Abs(vInvoiceLineItem.TAXE_3_AMOUNT.GetValueOrDefault(0));
                        }
                        if (vInvoiceLineItem.PATIENT_SUBTOTAL.GetValueOrDefault(0) > 0)
                        {
                            vJournalEntryLineItem.JournalEntryLineDetailPostingType = FiloptoQBOConstants.QBOCredit;
                        }
                        else
                        {
                            vJournalEntryLineItem.JournalEntryLineDetailPostingType = FiloptoQBOConstants.QBODebit;
                        }
                        vJournalEntryLineItem.JournalEntryLineDetailAccountRefId = GetItemTypeFeesAccountID(vInvoiceLineItem.ITEMTYPE);
                        vJournalEntryLineItem.Description = $"{vInvoiceMemo}, Line Item: {vInvoiceLineItem.STATEMENT}";
                        vJournalEntry.JournalEntryLineItems.Add(vJournalEntryLineItem);
                    }
                    #endregion
                    QBOEntities.SaveChanges();
Any help would be appreciated.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by Shalex » Thu 11 May 2017 18:33

We are processing your request. We will notify you about the result as soon as possible.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by Shalex » Mon 15 May 2017 18:29

We have reproduced the issue and are investigating it. We will notify you about the result.

danielrail
Posts: 11
Joined: Tue 10 Feb 2015 18:22

Re: dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by danielrail » Thu 25 May 2017 13:12

Any update on the progress?

Have a nice day.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by Shalex » Wed 31 May 2017 12:01

Instead of

Code: Select all

    using (var QBOEntities = new Entities())
    {
        var vJournalEntryLineItem = QBOEntities.JournalEntryLineItems.CreateObject();

        var vJournalEntry = QBOEntities.JournalEntries.CreateObject();
        vJournalEntry.JournalEntryLineItems.Add(vJournalEntryLineItem);

        QBOEntities.JournalEntries.AddObject(vJournalEntry);
        QBOEntities.SaveChanges(); // error: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'JournalEntryId'.
    }
please use the following code

Code: Select all

    using (var QBOEntities = new Entities())
    {
        // here we read some existing Line value but you can create your own JSON value
        var lineEntrySample = QBOEntities.JournalEntries.FirstOrDefault().Line;

        var vJournalEntry = QBOEntities.JournalEntries.CreateObject();
        vJournalEntry.Line = lineEntrySample;

        QBOEntities.JournalEntries.AddObject(vJournalEntry);
        QBOEntities.SaveChanges(); // works
    }
Explanation: JournalEntryLineItems is a database view created basing on the existing values JournalEntries.Line.

Does this help?

danielrail
Posts: 11
Joined: Tue 10 Feb 2015 18:22

Re: dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by danielrail » Wed 31 May 2017 13:13

Thanks for the reply,

So if I understand correctly, I would need to assemble the journal entry (header and line items) as JSON and then assign it to JournalEntry.Line.

If that is the case, then it kinds of defeat the purpose to use the Entity Framework to try to simplify the work.

And, since there is no documentation on that property (JournalEntry.Line), I wouldn't have known what it is used for.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for QuickBooks: Adding a JournalEntry via Entity Framework 6

Post by Shalex » Thu 01 Jun 2017 18:28

danielrail wrote:So if I understand correctly, I would need to assemble the journal entry (header and line items) as JSON and then assign it to JournalEntry.Line.
That is correct.
danielrail wrote:If that is the case, then it kinds of defeat the purpose to use the Entity Framework to try to simplify the work.
We will investigate the possibility to improve this use case and notify you about the result.

Post Reply