Use linq to match up pairs of rows in a set

Question

In the system I use modifications to data are received in pairs of rows old and new with a RowMod flag, for example deleted, added, updated and unchanged rows come through as:

RowID Data    RowMod
Row1  "fish"  "" 
Row1  "fish"  "D" 
Row2  "cat"   "A"
Row3  "fox"   ""
Row3  "dog"   "U"
Row4  "mouse" ""

I'd like to match these up using the RowID that each row has and get something like:

RowID OldData NewData RowMod
Row1  "fish"  null    "D"
Row2  null    "cat"   "A"
Row3  "fox"   "dog"   "U"
Row4  "mouse" "mouse" ""

Answer 1

class Program
{
    static void Main(string[] args)
    {
        IEnumerable<DataRow> rows = new[]
        {
            new DataRow(1,"fish",""),
            new DataRow(1,"fish","D"),
            new DataRow(2,"cat","A"),
            new DataRow(3,"fox",""),
            new DataRow(3,"dog","U"),
            new DataRow(4,"mouse","")
        };

        var result = rows
            .GroupBy(x => x.Id)
            .Select(g => new 
            {
                Count = g.Count(),
                Id = g.First().Id,
                FirstRow = g.First(),
                LastRow = g.Last() 
            }).Select(item => new 
            {
                RowId = item.Id,
                OldData = item.Count == 1 && item.FirstRow.RowMod != "" ? null : item.FirstRow.Data,
                NewData = item.LastRow.RowMod == "D" ? null : item.LastRow.Data,
                RowMod = item.LastRow.RowMod 
            });

            //Or using query syntax
            var result2 = from x in rows
                          orderby x.Id, x.RowMod
                          group x by x.Id into g
                          select new
                          {
                              RowId = g.First().Id,
                              OldData = g.Count() == 1 && g.First().RowMod != "" ? null : g.First().Data,
                              NewData = g.Last().RowMod == "D" ? null : g.Last().Data,
                              RowMod = g.Last().RowMod
                          };

        // Test
        Console.WriteLine("RowID\tOldData\tNewData\tRowMod");
        foreach (var item in result)
        {
            Console.WriteLine("{0}\t'{1}'\t'{2}'\t'{3}'",item.RowId,item.OldData ?? "null",item.NewData ?? "null",item.RowMod);
        }
    }
}

public class DataRow
{
    public int Id { get; set; }
    public string Data { get; set; }
    public string RowMod { get; set; }

    public DataRow(int id, string data, string rowMod)
    {
        Id = id;
        Data = data;
        RowMod = rowMod;
    }
}

Output:

RowID   OldData NewData RowMod
1       'fish'  'null'  'D'
2       'null'  'cat'   'A'
3       'fox'   'dog'   'U'
4       'mouse'  'mouse' ''

Answer 2

I guess there are more elegant ways to do it, but this produces the output you expect:

public class MyClass
            {
                public int RowID { get; set; }
                public string Data { get; set; }
                public string RowMod { get; set; }
            }

  var result = (from id in myList.Select(x => x.RowID).Distinct()
            let oldData = myList.Where(x => x.RowID == id).SingleOrDefault(x => x.RowMod.Equals("")) != null
                ? myList.Where(x => x.RowID == id).Single(x => x.RowMod.Equals("")).Data
                : null
            let newData = myList.Where(x => x.RowID == id).SingleOrDefault(x => !x.RowMod.Equals("")) != null
                ? myList.Where(x => x.RowID == id).Single(x => !x.RowMod.Equals("")).Data
                : null
            let rowMod = myList.Where(x => x.RowID == id).SingleOrDefault(x => !x.RowMod.Equals("")) != null
                ? myList.Where(x => x.RowID == id).Single(x => !x.RowMod.Equals("")).RowMod
                : null
            select new
                   {
                       RowID = id,
                       OldData = oldData,
                       NewData = rowMod == null ? oldData : rowMod.Equals("D") ? null : newData,
                       RowMod = rowMod
                   });
foreach (var item in result)
            {
                Console.WriteLine("{0} {1} {2} {3}", item.RowID, item.OldData ?? "null", item.NewData ?? "null", item.RowMod ?? "-");
            }

Answer 3

While I love LINQ a lot, I don't think it is appropriate here as you want to buffer some values while iterating. If you do this with LINQ, it will be at best not performing well, at worst it will iterate the collection multiple times. It also looks way cleaner this way in my opinion.

IEnumerable<TargetClass> MapOldValues(IEnumerable<SourceClass> source)
{
    var buffer = new Dictionary<string, string>();
    foreach(var item in source)
    {
        string oldValue;
        buffer.TryGetValue(item.RowId, out oldValue); 
        yield return new TargetClass
                          {
                              RowId = item.RowId, 
                              OldData = oldValue, 
                              NewData = (item.RowMod == "D" ? null : item.Data), 
                              RowMod = item.RowMod  };
        // if the rows come sorted by ID, you can clear old values from
        // the buffer to save memory at this point:
        // if(oldValue == null) { buffer.Clear(); }
        buffer[item.RowId] = item.Data;
    }
}

if you then only want the latest updates, you can go with LINQ:

var latestChanges = MapOldValues(source).GroupBy(x => x.RowId).Select(x => x.Last());

Answer 4

I am not sure if this is the best way to achieve your requirement but this is what I have:-

var result = rows.GroupBy(x => x.RowId)
                 .Select(x => 
          {
             var firstData = x.FirstOrDefault();
             var secondData = x.Count() == 1 ? x.First().RowMod == "A" ? firstData : null
                                             : x.Skip(1).FirstOrDefault();
          return new
          {
              RowId = x.Key,
              OldData = firstData.RowMod == "A" ? null : firstData.Data,
              NewData = secondData != null ? secondData.Data : null,
              RowMod = String.IsNullOrEmpty(firstData.RowMod) && secondData != null ?
                                      secondData.RowMod : firstData.RowMod
          };
      });

Working Fiddle.

Answer 5

Getting the two parts of the intended object can be done iteratively:

foreach(var rowId in myList.Select(x => x.RowId).Distinct())
{
    //get the left item
    var leftItem = myList.SingleOrDefault(x => x.RowId == rowId && String.IsNullOrWhiteSpace(x.rowmod);

    //get the right item
    var rightItem = myList.SingleOrDefault(x => x.RowId == rowId && !String.IsNullOrWhiteSpace(x.rowmod);
}

Your question doesn't specify how you create the second object. Is it a different class? Either way, you can extrapolate from the above snippet that either item might be null if it doesn't exist in the original set.

All you need to do is use those found objects to create your new object.