Linq to objects nested group-by

Question

I have this query working but it is not returning back exactly what I am looking for.

I have a collection of:

List<TransactionRecord> transactionLog;

TransactionRecord simplified looks something like this:

class TransactionRecord {
   public string txSetComments;
   public string BatchComments;
   public string TargetComments;
   public string TargetName;
   public string TargetValue;
}

and might be initialized as:

List<TransactionRecord> transactionLog = new List<TransactionRecord>()
{     
    new TransactionRecord { txSetComments = "txc1", 
                            BatchComments = "bc1",
                            TargetComments = "tc1", 
                            TargetName = "target1",
                            TargetValue = "v1" },

    new TransactionRecord { txSetComments = "txc1",
                            BatchComments = "bc1",
                            TargetComments = "tc1", 
                            TargetName = "target1",
                            TargetValue = "v2" },

    new TransactionRecord { txSetComments = "txc2", 
                            BatchComments = "bc2",
                            TargetComments = "tc1", 
                            TargetName = "target2",
                            TargetValue = "v3" },

    new TransactionRecord { txSetComments = "txc2", 
                            BatchComments = "bc1",
                            TargetComments = "tc1", 
                            TargetName = "target2",
                            TargetValue = "v4" },

    new TransactionRecord { txSetComments = "txc1", 
                            BatchComments = "bc3",
                            TargetComments = "tc1", 
                            TargetName = "target1",
                            TargetValue = "v5" },

    new TransactionRecord { txSetComments = "txc3",     
                            BatchComments = "bc3",
                            TargetComments = "tc1", 
                            TargetName = "target3",
                            TargetValue = "v6" }          
};

Here is the query so far:

Dictionary<string, Dictionary<string, IEnumerable<TransactionRecord>>> history =
    transactionLog.GroupBy(tx => tx.TxSetComments)
        .ToDictionary(g => g.Key,
                      g => g.GroupBy(b => b.BatchComments).ToDictionary(e => e.Key,
                                                                        e => e.Where(t => t.TargetName == Target)));

Here is the problem. If I set "Target" in the query to "target1", most of the result is as I would expect:

txc1
   bc1
      target1/v1 
      target1/v2
   bc3
      target1/v5

This is a good start, but I also get:

txc2
txc3

added to the list for a complete result that looks likes like:

txc1
   bc1
      target1/v1 
      target1/v2
   bc3
      target1/v5
txc2
txc3

I would like the query to only return top level groups if there is a match to "Target". In the above example it still returns "txc2" and "txc3" as top level groups even though they do not match the "Target".

I hope this is not too confusing. Any recommendations?

Thanks!

Answer 1

Copy your Where clause outside the GroupBy's.

var history = transactionLog.Where(record => record.TargetName == "target1")
    .GroupBy(tx => tx.txSetComments)
    .ToDictionary(
        g => g.Key,
        g => g.GroupBy(b => b.BatchComments)
               .ToDictionary(e => e.Key,
                             e => e.Where(t => t.TargetName == "target1"));