C# Compare and Group Rows Based on Multiple Conditions

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]

Can you post some code of what you have tried so far?
Without having much an idea of how do you read your data, but based on your linq tag, I would guess that you have access to classes.

Based on some assumptions about your code, I would do something like

var newValues = new List<Value>();
var groupBy = values.GroupBy(x=>$"{x.SHOPID}{x.ITEMID}");
foreach(var group in groupBy){
    var oldRow = group.First(x=> x.NEW_BATCHNO == "");
    var newRow = group.First(x=> x.OLD_BATCHNO == "");
    newValues.Add(new Value(){
        SHOPID = oldRow.SHOPID,
        ITEMID = oldRow.ITEMID,
        OLD_BATCHNO = oldRow.OLD_BATCHNO,
        NEW_BATCHNO = newRow.NEW_BATCHNO,
        OLD_QTY = oldRow.NEW_BATCHNO,
        NEW_QTY = newRow.NEW_BATCHNO,
        };
}
return newValues;

Try this, I try to write a simple query.

var result = datatable
  .Where(d => (d.SHOPID, d.ITEMID) 
    == (datatable.FirstOrDefault(dd => dd.OLD_BATCHNO == dd.NEW_BATCHNO)
        ?? new { SHOPID = default(string), ITEMID = default(string) }))
  .Where(d => d.OLD_BATCHNO == null || d.OLD_BATCHNO != d.NEW_BATCHNO || d.OLD_QTY != d.NEW_QTY);

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.