I have a Datatable of items and their specific related data such as cost, tax information quantity etc. One of the columns in this datatable is Invoice Number. The same value of the Invoice Number is repeated for all the different items present in one such set. I have 100s of such sets in one Datatable.
What I need to calculate is the sum of the 'cost of Item column' and sum of the 'total tax on the item' column for each invoice number and update in the same table for all the rows that belong to the same invoice number. I will give an example to explain this below in the form of a table.
Basically i have been able to accomplish this by using for loops and data views etc. but by doing all that my code is iterating through the data table multiple times, which is hitting on the performance very badly.
I was hoping if some one of you would be able to help me with the Linq queries that I can fire on the datatable and the get the values in 1 shot, and update the same for all the rows in one single For loop.
Any other suggestion also would be much appreciated.
Please do not ask me, why is the data like this, and why can't we change the data structure, because we can not. This is a real world example, and all i need is a little guidance on getting the results in a single shot, rather that iterating multiple times in the same data table.
The problem is not with 10 to 15 rows, that is manageable, the problem comes when i have more than 130 rows in the Data table, that is when it starts impacting the performance of the code.
Two Tables, Top one is Source Datatable and bottom is To be Datatable
Currently what I am doing is, I am first creating a temp table with unique Invoice Numbers. Then I am looping through all the rows of my Data Table and finding the total of all the items and the total of the tax amount for each invoice from the unique invoice table. Then I am looping again through the original datatable to update the total item amount and total tax amount for the respective invoices in the original datatable.
This is causing me to loop on the same data table multiple times, also i am unable to use exit for statement because there is a possibility that the invoice number can be present anywhere in the datatable, so basically for every invoice identified in the unique invoice table for calculating as well as updating the values back in the datatable i am forced to run through the entire data table.
Instead what I was hoping was, if there could be any mechanism where we can find the total of the item amount and total of tax amount for a specific invoice number directly and update it in one shot in a single for loop on the datatable.
I can do this very easily using SQL queries, but not sure how to write the Linq queries for this job. The problem is this datatable is not present in this format in the database, I am performing a lot of operations on this datatable through my code, and this step comes towards the end of the overall activity.
Dim dv1 As DataView = my_format_dt.DefaultView
Dim unique_invoice_dt As DataTable = dv1.ToTable(True, "supplier_id_invoice_number_combo")
If unique_invoice_dt IsNot Nothing AndAlso unique_invoice_dt.Rows.Count > 0 Then
For row_cntr_unique_invoices = 0 To unique_invoice_dt.Rows.Count - 1
Dim unique_combo As String = unique_invoice_dt.Rows(row_cntr_unique_invoices).Item("supplier_id_invoice_number_combo").ToString
Dim total_item_amount As Double = 0
Dim total_tax_amount As Double = 0
For row_cntr2 = 0 To my_format_dt.Rows.Count - 1
Dim check_string As String = my_format_dt.Rows(row_cntr2).Item("supplier_id_invoice_number_combo").ToString
If check_string = unique_combo Then
total_item_amount = total_item_amount + Convert.ToDouble(my_format_dt.Rows(row_cntr2).Item("total_cost_price_of_item").ToString)
Dim cgst_percentage As Double = Convert.ToDouble(my_format_dt.Rows(row_cntr2).Item("cgst_percentage").ToString)
Dim sgst_percentage As Double = Convert.ToDouble(my_format_dt.Rows(row_cntr2).Item("sgst_percentage").ToString)
Dim cgst_amount As Double = Math.Round(((cgst_percentage / 100) * Convert.ToDouble(my_format_dt.Rows(row_cntr2).Item("total_cost_price_of_item").ToString)), 2)
Dim sgst_amount As Double = Math.Round(((sgst_percentage / 100) * Convert.ToDouble(my_format_dt.Rows(row_cntr2).Item("total_cost_price_of_item").ToString)), 2)
total_tax_amount = total_tax_amount + cgst_amount + sgst_amount
End If
Next
For row_cntr3 = 0 To my_format_dt.Rows.Count - 1
Dim check_string As String = my_format_dt.Rows(row_cntr3).Item("supplier_id_invoice_number_combo").ToString
If check_string = unique_combo Then
my_format_dt.Rows(row_cntr3).Item("invoice_item_amount") = Math.Round(total_item_amount, 2)
my_format_dt.Rows(row_cntr3).Item("invoice_tax_amount") = Math.Round(total_tax_amount, 2)
End If
Next
Next
End If
So in my code, my_format_dt is my main Data Table. Using this data table I am creating a temp dt with only unique invoices. Then picking those unique invoices one by one, i am calculating the total item amount and total tax amount by looping through all the rows of my main datatable, then once I get the total item amount and total tax amount, I am again looping through the entire datatable to fill the values for each and every row that belongs to the unique invoice number. Basically I feel I am looping around the main datatable way more that what is required. What is the more efficient way to accomplish this