Grouping by multiple rows/multiple columns to create composite 'View'

Question

I have a context defined that exposes a 'Record' that has a child collection of 'Result'. Each result has a ResultType and each Record can have multiple results of each type;

public class Record : Entity<int>
{
    public int Count { get; set; }

    public virtual List<Result> Results { get; set; } = new List<Result>();
}

public class Result : Entity<int>
{
    public int RecordId { get; set; }

    public Record Record { get; set; }

    public ResultType ResultType { get; set; }

    public string Value1 { get; set; }

    public string Value2 { get; set; }
}

I am looking to return a list of all Records that are grouped/summed by the combined data in Result.Value1 and Result.Value2 by ResultType, with a sum of Record.Count.

For example,

Record 1 : Count - 10
 - Result (ResultType = 1, Value1 = "value1", Value2 = "value2")
 - Result (ResultType = 1, Value1 = "other1", Value2 = "other2")
 - Result (ResultType = 0, Value1 = "a", Value2 = "b")

Record 2 : Count - 10
 - Result (ResultType = 1, Value1 = "value1", Value2 = "value2")
 - Result (ResultType = 1, Value1 = "other1", Value2 = "other2")
 - Result (ResultType = 0, Value1 = "a", Value2 = "b")

Record 3 : Count - 50
 - Result (ResultType = 1, Value1 = "value1", Value2 = "value2)
 - Result (ResultType = 0, Value1 = "a", Value2 = "b)

Would give 2 rows, with a combined value for the Results;

Count = 20, CombinedResultType0 = "a#b", CombinedResultType1 = "value1|value2,other1|other2"
Count = 50, CombinedResultType0 = "a#b", CombinedResultType1 = "value1|value2"

I am using Linq to Entities but not able to figure out how to do the concatination and grouping in single, or multiple statements. At present, I am getting around this problem by creating a composite view of the data at the point I insert it (CombinedResultType0/1) and add this to the Record and then simply grouping by that column without storing each 'Result'. Unfortunately, this doesn't give me a clean way of querying to results directly.

Answer 1

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Record> records = new List<Record>() {
                new Record() {
                    Count = 10,
                    Results = new List<Result>() {
                        new Result() { ResultType = ResultType.One, Value1 = "value1", Value2 = "value2"},
                        new Result() {ResultType = ResultType.One, Value1 = "other1", Value2 = "other2"},
                        new Result() {ResultType = ResultType.Zero, Value1 = "a", Value2 = "b"}
                    }
                },
                new Record() {
                    Count = 10,
                    Results = new List<Result>() {
                       new Result() {ResultType = ResultType.One, Value1 = "value1", Value2 = "value2"},
                       new Result() {ResultType = ResultType.One, Value1 = "other1", Value2 = "other2"},
                       new Result() {ResultType = ResultType.Zero, Value1 = "a", Value2 = "b"}
                    }
                },
                new Record() {
                    Count = 50,
                    Results = new List<Result>() {
                       new Result() {ResultType = ResultType.One, Value1 = "value1", Value2 = "value2"},
                       new Result() {ResultType = ResultType.Zero, Value1 = "a", Value2 = "b"}
                    }
                }
            };

            var temp = records.Select(x => new { 
                count = x.Count, 
                CombinedResultType0 = x.Results.Where(y => y.ResultType == ResultType.Zero).Select(z => z.Value1 + "#" + z.Value2).FirstOrDefault(),
                CombinedResultType1 = string.Join(",",x.Results.Where(y => y.ResultType == ResultType.One).Select(z => z.Value1 + "|" + z.Value2))
            }).ToList();

            var results = temp.GroupBy(x => new { zero = x.CombinedResultType0, one = x.CombinedResultType1 })
                .Select(x => new { count = x.Sum(y => y.count), CombinedResultType0 = x.Key.zero, CombinedResultType1 = x.Key.one })
                .ToList();
        }
    }

    public class Record 
    {
        public int Count { get; set; }
        public virtual List<Result> Results { get; set; }
    }
    public class Result
    {
        public int RecordId { get; set; }
        public Record Record { get; set; }
        public ResultType ResultType { get; set; }
        public string Value1 { get; set; }
        public string Value2 { get; set; }
    }
    public enum ResultType
    {
        Zero,
        One
    }
}