Conditional Group By statement using LINQ

Question

I have what seems to be a fairly simple requirement, but looking around I'm not able to get a simple answer for this. I have looked on MSDN forums, Exper Exchange and nothing substantial was given to me. I have the following LINQ code

Dim SummaryLog As IQueryable(Of clPartCountSummary)    
SummaryLog = From Inventory In db.tblPartCounts _
            Where Inventory.InventoryCountId = InventoryCountId _
            And Not Inventory.ActionId.HasValue _
            Group By PartNumber = Inventory.PartNumber _
            , Inventory.RevLevel, SAPLocation = Inventory.SAPLocation _
            Into AggregatedProdLog = Group, Qty = Sum(Inventory.Quantity) _
            Select New clPartCountSummary With 
                {.PartNumber = PartNumber, 
                 .RevLevel = RevLevel, 
                 .Qty = Qty, 
                 .SAPLocation = SAPLocation}

I want to be able to conditionally group by on RevLevel and SAPLocation. I will always group by PartNumber, but the other two are optional. So if a variable bRevLevel is true then we group by rev level and if bSAPLocation is true then we group by SAP Location as well.

Any help will be much appreciated, I'm at the stage where multiple SummaryLog definitions are starting to look appealing.

Thanks, Tomasz

Answer 1

Hope this helps....

public class TblPartCount
{
    public int InventoryCountID { get; set; }
    public int? ActionID { get; set; }
    public string PartNumber { get; set; }
    public int RevLevel { get; set; }
    public string SAPLocation { get; set; }
    public int Quantity { get; set; }
}

private static void ConditionalGroupBy()
    {
        bool pCheck = true;

        var lList = new List<TblPartCount>
                        {
                            new TblPartCount { InventoryCountID = 1, ActionID = 2, PartNumber = "123", RevLevel = 1, SAPLocation = "AAAA", Quantity = 100 },
                            new TblPartCount { InventoryCountID = 1, ActionID = 2, PartNumber = "123", RevLevel = 1, SAPLocation = "BBBB", Quantity = 200 }
                        };

        var lOutput = lList
                          .Where(pArg => pArg.InventoryCountID == 1)
                          .Where(pArg => pArg.ActionID != null)
                          .GroupBy(pArg => new
                          {
                              PartNumber = pArg.PartNumber,
                              RevLevel = pCheck ? pArg.RevLevel : 0,
                              SAPLocation = pCheck ? pArg.SAPLocation : String.Empty
                          })
                          .Select(pArg =>
                                    new
                                    {
                                        PartNumber = pArg.Key.PartNumber,
                                        RevLevel = pArg.Key.RevLevel,
                                        SAPLocation = pArg.Key.SAPLocation,
                                        Qtry = pArg.Sum(pArg1 => pArg1.Quantity)
                                    });

        foreach (var lItem in lOutput)
        {
            Console.WriteLine(lItem);
        }
    }