Conditional Group Using LINQ

Question

I have what seems like a fairly simple requirement, but looking back, I can't get a simple answer. I looked at the MSDN, Exper Exchange forums and gave me nothing significant.

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 RevLeveland SAPLocation. I will always group with help PartNumber, but the other two are optional. So, if the variable bRevLevelis true, then we group by RevLevel, and if bSAPLocationtrue, then we also group SAPLocation.

Any help would be greatly appreciated; I am at a stage where several definitions SummaryLogare starting to look attractive.

Thanks Tomasz

Answer 1

, ...

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);
        }
    }