C# Compare and Group Rows Based on Multiple Conditions

Question

Given a datatable with the following rows.

SHOPID   ITEMID   OLD_BATCHNO   NEW_BATCHNO   OLD_QTY   NEW_QTY

SHOP01   ITEM01    BATCH0001                     1        
SHOP01   ITEM01                  BATCH0001                 1
SHOP02   ITEM02    BATCH0002                     2         
SHOP02   ITEM02                  BATCH0002                 3
SHOP03   ITEM03    BATCH0003                     4        
SHOP03   ITEM03                  BATCH0003                 5
SHOP04   ITEM04    BATCH0004                     4
SHOP05   ITEM05                  BATCH0005                 5

Expected Result:

SHOPID   ITEMID   OLD_BATCHNO   NEW_BATCHNO   OLD_QTY   NEW_QTY

SHOP02   ITEM02    BATCH0002     BATCH0002       2         3
SHOP03   ITEM03    BATCH0003     BATCH0003       4         5
SHOP04   ITEM04    BATCH0004                     4
SHOP05   ITEM05                  BATCH0005                 5

I want to fetch all rows that meets the following condition:

• Matched SHOPID
• and
• Matched ITEMID
• and
  • No match of [OLD_BATCHNO - NEW_BATCHNO]
  • or
  • Matched [OLD_BATCHNO - NEW_BATCHNO] but no match of [OLD_QTY - NEW_QTY]

Answer 1

If the rows in your datatable are of type Item and the rows are collected in a List<Item> items, this should be one possible approach:

var groupedItems = items
    .GroupBy(item => item.SHOPID + item.ITEMID);

var filteredGroupedItems = groupedItems
    .Where(gr =>
        gr
           .Select(item => item.OLD_BATCHNO)
           .Intersect(gr.Select(item => item.NEW_BATCHNO))
           .All(string.IsNullOrEmpty) ||
        gr
           .Select(item => item.OLD_QTY)
           .Intersect(gr.Select(item => item.NEW_QTY))
           .All(qty => !qty.HasValue));

var resultingItems = filteredGroupedItems
    .Select(gr => new Item
        (
            gr.First().SHOPID,
            gr.First().ITEMID,
            gr.FirstOrDefault(item => !string.IsNullOrEmpty(item.OLD_BATCHNO))?.OLD_BATCHNO,
            gr.FirstOrDefault(item => !string.IsNullOrEmpty(item.NEW_BATCHNO))?.NEW_BATCHNO,
            gr.FirstOrDefault(item => item.OLD_QTY.HasValue)?.OLD_QTY,
            gr.FirstOrDefault(item => item.NEW_QTY.HasValue)?.NEW_QTY
        ))
    .ToList();

The groupedItems object fulfills the Matched SHOPID and Matched ITEMID requirement.

The filteredGroupedItems object fulfills the 'one of' requirement of the following two requirements:

• No match of [OLD_BATCHNO - NEW_BATCHNO]
• (Matched [OLD_BATCHNO - NEW_BATCHNO] but) no match of [OLD_QTY - NEW_QTY]

The resultingItems objects is a collection of the 'merged' items of each filtered grouping.

Given your example input, the corresponding output is as follows:

SHOP02   ITEM02   BATCH0002   BATCH0002   2   3
SHOP03   ITEM03   BATCH0003   BATCH0003   4   5
SHOP04   ITEM04   BATCH0004               4    
SHOP05   ITEM05               BATCH0005       5

Example fiddle here.