Merge two rows into single row based on a column using LINQ C#

Question

I have a object list like below. I want to join every two rows into single row based on column B. It is sure that only two rows would be there for every single column B value.

Input

Output

However, I have done it and solution works. but I am looking for more better solution. I am not much happy with my solution.

My solution:

var groupByItems = items.GroupBy(x => x.ColumnB).Select(x => new MappingClass
        {
            ColumnA= x.FirstOrDefault().ColumnA,
            ColumnB= x.FirstOrDefault().ColumnB,
            ColumnC= x.Where(r=> !string.IsNullOrEmpty(r.ColumnC)).Select(r=>r.ColumnC).FirstOrDefault(),
            ColumnD= x.Where(r => !string.IsNullOrEmpty(r.ColumnD)).Select(r => r.ColumnD).FirstOrDefault(),
        }).ToList();

Now groupByItems object returns me two rows as expected.

Answer 1

You can use Key of the Groups generated by GroupBy() Also, there's no need to use .Where() you can simply put your filter as a lambda expression in .FirstOrDefault() for ColumnC & ColumnD

var groupByItems = items.GroupBy(x => new { ColumnA = x.ColumnA, ColumnB = x.ColumnB })
                        .Select(x => new MappingClass
                        {
                            ColumnA = x.Key.ColumnA,
                            ColumnB = x.Key.ColumnB,
                            ColumnC = x.FirstOrDefault(m => !string.IsNullOrEmpty(m.ColumnC)).ColumnC,
                            ColumnD = x.FirstOrDefault(m => !string.IsNullOrEmpty(m.ColumnD)).ColumnD
                        })
                        .ToList();