Update a datatable from another datatable using LINQ

Question

In my C# code I have two datatable like this:

inventory_table

SKU	Title	Price	Quantity	Handling-Time
SAM-GAL-S7-32	Samsung Galaxy S7 128GB	300.00	5	Cell 2
SAM-GAL-S6-64	Samsung Galaxy S6 64GB	250.00	10	Cell 2
SAM-GAL-NOTE-S20-128	Samsung Galaxy Note S20 128GB	275.00	7	Cell 2
IP-13P-128	Apple iPhone 13 Pro 128GB	750.00	9	Cell 2
IP-14-128	Apple iPhone 14 128GB	800.00	4	Cell 2
IP-12-64	Apple iPhone 12 64GB	350.00	6	Cell 2
LG-V60-128	LG V60 128GB	250.00	3	Cell 2

order_table

SKU	Quantity
SAM-GAL-S6-64	2
IP-13P-128	1
LG-V60-128	1

I want to substract the quantity of order_table from the inventory_table for the matching SKU, so the result should be:

inventory_table (updated)

SKU	Title	Quantity
SAM-GAL-S7-32	Samsung Galaxy S7 128GB	5
SAM-GAL-S6-64	Samsung Galaxy S6 64GB	8
SAM-GAL-NOTE-S20-128	Samsung Galaxy Note S20 128GB	7
IP-13P-128	Apple iPhone 13 Pro 128GB	8
IP-14-128	Apple iPhone 14 128GB	4
IP-12-64	Apple iPhone 12 64GB	6
LG-V60-128	LG V60 128GB	2

The rows with SKU "SAM-GAL-S6-64", "IP-13P-128" and "LG-V60-128" now have updated quantity. These tables only exists in memory, not in database, and the result table will be exported to a text file. In reality, inventory_table would have a few thousand rows while order_table has 20-30 rows. I believe this task can be done with LINQ. Please advise. Thanks

Answer 1

For example, if the Quantity column is int:

DataTable inventory_table = . . .
DataTable order_table = . . .

foreach( var p in inventory_table.AsEnumerable().Join(order_table.AsEnumerable(), r2 => r2["SKU"], r1 => r1["SKU"], ( r1, r2 ) => new { r1, r2 } ) )
{
    p.r1.SetField<int>( "Quantity", p.r1.Field<int>( "Quantity" ) - p.r2.Field<int>( "Quantity" ) );
}

If it works, but is slow, maybe it can be improved.