LINQ merge 2 query results

Question

The datatable has 5 columns

Name   Class   Course    Month    Score
Alex   C1      Math      12       90
Bob    C1      Chem      11       91
Alex   C2      Math      11       91
Alex   C1      Math      11       89
Bob    C1      Chem      12       97
Alex   C1      Math      10       94
Alex   C2      Chem      12       92
Bob    C2      Math      12       94

And I wanna group (name, class) and fetch the max math score in just Nov and Dec, and the max chem score. Heres my query code

DataRow[] dr1 = dt.Select("Course = 'Math' AND Month > 10");
var result_one = dr1.AsEnumerable()
    .GroupBy(r => new { Name = r.Field<string>("Name"), Class = r.Field<string>("Class") })
    .Select(g => new
    {
        Name = g.Key.Name, 
        Class = g.Key.Class, 
        Max = g.Max(r => r.Field<int>("Score")),
        Max_Month = g.FirstOrDefault(gg => gg.Field<int>("Score") == g.Max(r => r.Field<int>("Score"))).Field<int>("Month"),
    }
    ).Distinct().ToList();


DataRow[] dr2 = dt.Select("Course = 'Chem'");
var result_two = dr2.AsEnumerable()
    .GroupBy(r => new { Name = r.Field<string>("Name"), Class = r.Field<string>("Class") })
    .Select(g => new
    {
        Name = g.Key.Name,
        Class = g.Key.Class,
        Max = g.Max(r => r.Field<int>("Score")),
        Max_Month = g.FirstOrDefault(gg => gg.Field<int>("Score") == g.Max(r => r.Field<int>("Score"))).Field<int>("Month"),
    }
    ).Distinct().ToList();

And I could output these 2 query results as this:

Name  Class    Math_Max_Month    Math_Max
Alex   C1      12                90
Alex   C2      11                91
Bob    C2      12                94
Name  Class    Chem_Max_Month    Chem_Max
Bob    C1      12                97
Alex   C2      12                92

But how can I merge these 2 results into 1 output such as this:

Name  Class    Math_Max_Month    Math_Max  Chem_Max_Month   Chem_Max
Alex   C1      12                90        null             null
Alex   C2      11                91        12               92
Bob    C1      null              null      12               97
Bob    C2      12                94        null             null

I've tried to use result_one.Concat(result_two) and result_one.Union(result_two), but both are incorrect.

Answer 1

This works perfectly well for your code.,

DataRow[] dr1 = dtt.Select("Course = 'Math' AND Month > 10");
        var result_one = dr1.AsEnumerable()
            .GroupBy(r => new { Name = r.Field<string>("Name"), Class = r.Field<string>("Class") })
            .Select(g => new
            {
                Name = g.Key.Name,
                Class = g.Key.Class,
                Max = g.Max(r => r.Field<int>("Score")),
                Max_Month = g.FirstOrDefault(gg => gg.Field<int>("Score") == g.Max(r => r.Field<int>("Score"))).Field<int>("Month"),
            }
            ).Distinct().ToList();


DataRow[] dr2 = dtt.Select("Course = 'Chem'");
        var result_two = dr2.AsEnumerable()
            .GroupBy(r => new { Name = r.Field<string>("Name"), Class = r.Field<string>("Class") })
            .Select(g => new
            {
                Name = g.Key.Name,
                Class = g.Key.Class,                    
                Chem_Max = g.Max(r => r.Field<int>("Score")),
                Chem_Max_Month = g.FirstOrDefault(gg => gg.Field<int>("Score") == g.Max(r => r.Field<int>("Score"))).Field<int>("Month"),
            }
            ).Distinct().ToList();

Left Join...

var lstLeftJoin = (from a in result_one
                           join b in result_two
                           on new { a.Name, a.Class } equals new { b.Name, b.Class }
                           into gj
                           from subpet in gj.DefaultIfEmpty()
                           select new { a.Name, a.Class, Math_Max_Month = a.Max_Month, Math_Max = a.Max, Chem_Max_Month = (subpet == null ? 0 : subpet.Chem_Max_Month), Chem_Max = (subpet == null ? 0 : subpet.Chem_Max) }).ToList();

Right Join...

  var lstRightJoin = (from a in result_two
                            join b in result_one
                            on new { a.Name, a.Class } equals new { b.Name, b.Class }
                            into gj
                            from subpet in gj.DefaultIfEmpty()
                            select new { a.Name, a.Class, Math_Max_Month = (subpet == null ? 0 : subpet.Max_Month), Math_Max = (subpet == null ? 0 : subpet.Max), a.Chem_Max_Month, a.Chem_Max }).ToList();

Finaly the Union...

var lstUnion = lstLeftJoin.Select(s => new { Name = s.Name, Class = s.Class, Math_Max_Month = s.Math_Max_Month, Math_Max = s.Math_Max, Chem_Max_Month = s.Chem_Max_Month, Chem_Max = s.Chem_Max }).Union(lstRightJoin.Select(s => new { Name = s.Name, Class = s.Class, Math_Max_Month = s.Math_Max_Month, Math_Max = s.Math_Max, Chem_Max_Month = s.Chem_Max_Month, Chem_Max = s.Chem_Max })).OrderBy(o => o.Name).ThenBy(c => c.Class).ToList();

RESULT

Name  Class    Math_Max_Month    Math_Max  Chem_Max_Month   Chem_Max
Alex   C1      12                90        null             null
Alex   C2      11                91        12               92
Bob    C1      null              null      12               97
Bob    C2      12                94        null             null

Answer 2

Alright, seems a bit complicated in your example. So i'll give you an answer on a int[] instead of DataRow[]

int[] first = new int[] { 3, 5, 6, 9, 12, 14, 18, 20, 25, 28 };
  int[] second = new int[] { 30, 32, 34, 36, 38, 40, 42, 44, 46, 48 };

  int[] result = first
    .Concat(second)
    .OrderBy(x => x)
    .ToArray();

Output will be

// 3, 5, 6, 9, 12, 14, 18, 20, 25, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48
  Console.Write(String.Join(", ", result));

theoretically this should work in your case, sense we're only dealing with arrays.