I need join 3 tables using a column flag called cashbillingtype_id, this determines what table will joined.
For example i have this Datatable called [CashBillings]:
cashbilling_id cashbillingtype_id
1 1
2 1
3 2
When:
cashbillingtype_id = 1 means: CashBillingsBills
cashbillingtype_id = 2 means: CashBillingsReturns
cashbillingtype_id = 3 means: CashBillingsCancellations
Now each table (Bills, Returns, Cancellations) have inside a column called cashbillingBRC_total i need to get this column data according the flag main datatable.
I Tryed:
(from CashBillings in _DataTable_Billings.AsEnumerable()
join CashBillingsTypes in _DataTable_BillingsTypes.AsEnumerable()
on CashBillings.Field<Int32>("cashbillingtype_id") equals CashBillingsTypes.Field<Int32>("cashbillingtype_id")
select new
{
cashbilling_id = CashBillings.Field<Int32>("cashbilling_id"),
cashbillingBRC_total = (CashBillingsTypes.Field<Int32>("cashbillingtype_id") == 1 ?
(from CashBillingsBills in _DataTable_BillingsBills.AsEnumerable()
where CashBillingsBills.Field<Int32>("cashbilling_id") == CashBillings.Field<Int32>("cashbilling_id")
select CashBillingsBills.Field<Double>("cashbillingbill_total")).LastOrDefault()
:
(CashBillingsTypes.Field<Int32>("cashbillingtype_id") == 2 ?
(from CashBillingsReturns in _DataTable_BillingsReturns.AsEnumerable()
where CashBillingsReturns.Field<Int32>("cashbilling_id") == CashBillings.Field<Int32>("cashbilling_id")
where CashBillingsReturns.Field<Int32>("cashbillingreturnstatus_id") == 1 // Only Processed
select CashBillingsReturns.Field<Double>("cashbillingreturn_total")).LastOrDefault()
:
(from CashBillingsCancellations in _DataTable_BillingsCancellations.AsEnumerable()
where CashBillingsCancellations.Field<Int32>("cashbilling_id") == CashBillings.Field<Int32>("cashbilling_id")
select CashBillingsCancellations.Field<Double>("cashbillingcancellation_total")).LastOrDefault())
),
}).Aggregate(DataTable_Billings, (dt, result) => { dt.Rows.Add(result.cashbilling_id,
result.cashbillingtype_id,
result.cashbillingtype_name,
result.cashbillingBRCstatus_id,
result.cashbillingBRCstatus_name,
result.cashbillingcustomer_fullname,
result.cellar_name,
result.cashbillingBRC_subtotal,
result.cashbillingBRC_discount,
result.cashbillingBRC_isv,
result.cashbillingBRC_total,
result.cashbillingBRC_date); return dt;
});
But this code is very very low efficient.
