This project is read-only.

Issue - Self Referential Table

Dec 2, 2011 at 10:37 PM

Chris,

I recently added a column "parentpaymentid" to a table "payments" which is a foreign-key to the primary key in the table.  So an item can have sub items.  This new column is a nullable (int).

When I use an external entity "invoice" and access the payments like this: invoice.paymentslist()  the list returned only returns those payments where parentpaymentid is not NULL.

This is not what I expected or desired, I expected all payments with that invoice id.  I attempted to remove the 'enforce' checkbox from the relationship in my model, as I wasn't quite sure what the effect would be, and nothing.

Any suggestions?

Steve 

Dec 12, 2011 at 3:44 PM

Any thoughts why this might be occurring?

Dec 13, 2011 at 4:14 PM

I have confirmed this issue for the NHDAL and there will be a fix in the next version.

Dec 14, 2011 at 5:14 PM

Do you have any idea when the release will occurr?  I'm in a bind without this fix, and would rather not attempt to self compile.

Thanks,

Steve

Dec 16, 2011 at 9:23 PM

There has been a new version posted.

Dec 19, 2011 at 5:18 PM

There is still something going on with a related scenario, I confirm that there this situation is fixed.

However, I will try my best to describe another situation/bug that showed up as soon as I introduced the self-referential key.  Maybe it's just best if I give you a sample LINQ.

Dim hist = From act In ArrearsActionCollection.RunSelect(Function(o) o.RepaymentScheduleID = _data.RepaymentScheduleID And (o.LeasePaymentID Is Nothing Or Not o.LeasePayments.IsVoid))
                   Order By act.ActionDate Descending
                   Select New With {.Date = act.ActionDate, .Type = act.Arrearsaction_arrearsactionstepArrearsActionStepItem.Description, .Amount = CType(If(act.LeasePaymentsItem Is Nothing, 0D, act.LeasePaymentsItem.Amount), Decimal), .User = HelperObject.GetUsernameFullName(act.CreatedBy), .Notes = act.Comments}

This query used to work (I have moved the datasource to a RunSelect() from the original _data.ArrearsActionList) and since the addition of the key in the payments table does not.  It appears to be fine until there is more than one 'payment action' where o.LeasePaymentID = #.  If more than one exists in the results, execution of the query results in the application not responding, stepping through the code indicates that the ArrearsAction Domain object is throwing a bunch of row errors.

Any thoughts?

Steve

Dec 22, 2011 at 2:00 PM

This is a bit of a complex scenario. Would it be possible for you to create a sample application and database that reproduces this issue and send it to chrisd@nhydrate.org?

Jan 4, 2012 at 3:41 PM

I have sent you a sample project.  Let me know if you need anything else.  

I didn't have a whole lot of time to dig deep in the sample, but at first glance it does seem to exhibit the same behaviour.

Steve

Jan 13, 2012 at 8:07 PM

any luck here?  Even in reproducing the issue?

 

Steve

Jan 21, 2012 at 1:14 AM

Yes. We have reproduced this issue and it will be in the next release.