LINQ Merging results in rows

Question

Hi. I have a LINQ query which returns a set of rows. The structure is:

NAME,    col1, col2, col3, col4
name1     1    null  null  null
name1    null   1    null  null
name1    null  null   1     1

As a result I want to have one row containing

name1     1     1     1     1

So I want to group those results by name and merge (sum?) the other columns so if I have not null in one of the rows in a column - I will receive anything except null.

Thanks for help!

Answer 1

public class AggregateRows
{
    class AA { public string A, B, C, D;}

    public void DoIt()
    {
        List<AA> a = new List<AA>(  ) 
        {
            new AA { A="1", B=null, C=null, D=null},
            new AA { A=null, B="1", C=null, D=null},
            new AA { A=null, B=null, C="1", D=null},
            new AA { A=null, B=null, C=null, D="1"},
        };

        var result = a.Aggregate( ( a1, a2 ) => new AA { A = a1.A ?? a2.A, B = a1.B ?? a2.B, C = a1.C ?? a2.C, D = a1.D ?? a2.D } );
        Console.WriteLine("{0}{1}{2}{3}",result.A,result.B,result.C,result.D);
    }
}

yields

1111

and

public class AggregateRows
{
    class AA
    {
        public string N, A, B, C, D;
    }

    public void DoIt()
    {
        List<AA> data = new List<AA>() 
        {
            new AA { N="Name", A="1", B=null, C=null, D=null},
            new AA { N="Name", A=null, B="2", C=null, D=null},
            new AA { N="Name", A=null, B=null, C="3", D=null},
            new AA { N="Name", A=null, B=null, C=null, D="4"},
            new AA { N="Name2", A="2", B=null, C=null, D=null},
            new AA { N="Name2", A=null, B="2", C=null, D=null},
            new AA { N="Name2", A=null, B=null, C="2", D=null},
            new AA { N="Name2", A=null, B=null, C=null, D="2"},
        };

        var results = data.GroupBy( a => a.N )
            .Select( k =>
            {
                var values = k.Aggregate( ( a1, a2 ) => new AA
                {
                    A = a1.A ?? a2.A,
                    B = a1.B ?? a2.B,
                    C = a1.C ?? a2.C,
                    D = a1.D ?? a2.D
                } );
                return new AA { N = k.Key, A = values.A, B = values.B, C = values.C, D = values.D };
            } );
        foreach ( var result in results )
            Console.WriteLine( "{0} {1}{2}{3}{4}", result.N, result.A, result.B, result.C, result.D );
    }
}

yields

Name 1234
Name2 2222

EDIT: In response to your clarification...

I guess you'll be able to take it from here then. If all you want to do is find out whether there is a column within the group, then the Any operator like in Bruno's answer is the way to go. Aggregate is only necessary if you're trying to actually visit all the values in order to do something more complex like summing them (although as Jon alluded to, Sum handles that specific case).

In short, what you want is grouping like in both the answer, and then within the group you either use Aggregate to merge row by row or multiple Any on the results of the GroupBy depending on which is clearer in your context (or more efficient if you have a large set of data within each group)

Answer 2

class MyObj
{
    public string Name { get; set; }
    public int? Col1 { get; set; }
    public int? Col2 { get; set; }
    public int? Col3 { get; set; }
    public int? Col4 { get; set; }
}

List<MyObj> l = new List<MyObj> {
    new MyObj {Name = "name1", Col1 = 1 },
    new MyObj {Name = "name1", Col2 = 1 },
    new MyObj {Name = "name1", Col3 = 1 },
    new MyObj {Name = "name1", Col4 = 1 }
};

var qry = from o in l
          group o by o.Name into g
          select new
          {
              Name = g.Key,
              Col1 = g.Any(e => e.Col1.HasValue) ? (int?)1 : null,
              Col2 = g.Any(e => e.Col2.HasValue) ? (int?)1 : null,
              Col3 = g.Any(e => e.Col3.HasValue) ? (int?)1 : null,
              Col4 = g.Any(e => e.Col4.HasValue) ? (int?)1 : null
          };