Update a datatable from another datatable with LINQ

[henryvuong]

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
SAM-GAL-S6-64	Samsung Galaxy S6 64GB	250.00	10
SAM-GAL-NOTE-S20-128	Samsung Galaxy Note S20 128GB	275.00	7
IP-13P-128	Apple iPhone 13 Pro 128GB	750.00	9
IP-14-128	Apple iPhone 14 128GB	800.00	4
IP-12-64	Apple iPhone 12 64GB	350.00	6
LG-V60-128	LG V60 128GB	250.00	3

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

SKU	Title	Price	Quantity	Handling-Time
SAM-GAL-S7-32	Samsung Galaxy S7 128GB	300.00	5
SAM-GAL-S6-64	Samsung Galaxy S6 64GB	250.00	8
SAM-GAL-NOTE-S20-128	Samsung Galaxy Note S20 128GB	275.00	7
IP-13P-128	Apple iPhone 13 Pro 128GB	750.00	8
IP-14-128	Apple iPhone 14 128GB	800.00	4
IP-12-64	Apple iPhone 12 64GB	350.00	6
LG-V60-128	LG V60 128GB	250.00	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. Note that the last column of the first table is empty. I believe this task can be done with LINQ. Please advise. Thanks

 

[Skydiver]

Is there any particular reason why you want to use LINQ? In general, LINQ is not used to update the source data. Additionally since LINQ borrows a lot from the functional programming realm, and in functional programming, you never update source data, only create new data, then it is even more reason not to use LINQ.

On the other hand, since you are writing in C#, and not in F# or some other functional language, nobody is going to come out with torches and pitchforks demanding to burn you at the stake if you do end up doing some updates on the source data.

Anyway, if you don't use LINQ, it pretty simple and lot easier to understand if you use something like this pseudo-code:

foreach inventoryRow in inventory_table.Rows
    foreach orderRow in order_table.Rows
        if orderRow.SKU == inventoryRow.SKU
            inventoryRow.Quantity -= orderRow.Quantity

Note that the pseudo-code above doesn't deal with the case with the inventory quantity goes down to zero or becomes negative.

 

[cjard]

Agree; add in that datatables ar e particularly hard to use LINQ on, and all in its a difficult endeavour

I too would go the way of SD's pseudo code but I'd do the operation the other way round

I would have strongly typed datatables because weakly typed ones are a pain to work with in general

Once strongly typed dataset had been defined, with an Inventory table that had a primary key on the SKU the code would look more like:

foreach(var o in stDataset.Orders)
{
  var inv = stDataset.Inventory.FindBySKU(o.SKU);
  inv.Quantity -= o.Quantity;
}

FindBySKU uses a dictionary-based index to enable fast retrieval of a row. The dataset generator generates this method based on the combination of columns you mark as primary key

Query datasets - Visual Studio (Windows)

Understand query datasets. Learn about dataset case sensitivity. Find a specific row in a data table, find rows by column values, and access related records.

learn.microsoft.com

Don't save the dataset in a text file; that's dumb because you then have to parse it. It has ReadXml and WriteXml methods built in for loading/saving itself from/to an xml file

As a result you can use them like a sort of cheapo database, but using an actual embedded database like SQLite or LiteDb is better

 

[henryvuong]

Is there any particular reason why you want to use LINQ? In general, LINQ is not used to update the source data. Additionally since LINQ borrows a lot from the functional programming realm, and in functional programming, you never update source data, only create new data, then it is even more reason not to use LINQ.

On the other hand, since you are writing in C#, and not in F# or some other functional language, nobody is going to come out with torches and pitchforks demanding to burn you at the stake if you do end up doing some updates on the source data.

Anyway, if you don't use LINQ, it pretty simple and lot easier to understand if you use something like this pseudo-code:

foreach inventoryRow in inventory_table.Rows
    foreach orderRow in order_table.Rows
        if orderRow.SKU == inventoryRow.SKU
            inventoryRow.Quantity -= orderRow.Quantity

Note that the pseudo-code above doesn't deal with the case with the inventory quantity goes down to zero or becomes negative.

Actually, no I don't have to use LINQ. I just thought LINQ is leaner code. Your code works, though I have to modify it, like so:

foreach (DataRow inventoryRow in inventory_table.Rows)
    foreach (DataRow orderRow in order_table.Rows)
        if (orderRow["sku"].ToString() == inventoryRow["sku"].ToString())
            inventoryRow["quantity"] = Convert.ToInt16(inventoryRow["quantity"]) - int.Parse(orderRow["quantityPurchased"].ToString());

The data of inventory_table is read from a database while order_table is from a text file, that's why they are treated slightly different here.
Thanks for your advise.

 

[cjard]

our code works, though I have to modify it, like so:

Mmm.. Code that works with untyped DataTables is particularly nasty looking, isn't it?

Also, that's O(n²) by the way

 

[jmcilhinney]

This:

if (orderRow["sku"].ToString() == inventoryRow["sku"].ToString())

would be better written like this:

if (orderRow.Field<string>("sku") == inventoryRow.Field<string>("sku"))

Don't convert when you can cast and should cast. If there's no reason for the data to not be a string, don't call ToString. That said, ToString will handle NULLs by converting DBNull.Value to string.Empty. If nullable reference types are supported though, you can do the same by using Field<string?>.