Sage Developers' Blog

Sometimes we need to work with data that should be grouped by a particular field or set of fields, but where we can’t be sure that there will always be data in each group.

For example, we might have a list of transactions, each one of which has been posted into a particular period. If I want to plot these on a chart, or pivot the data by period then I need to ensure that all of the period values are represented in the dataset, even though there may be some periods in which no transactions were posted. If no transactions were posted in period 2, then I want to see a gap in my chart, or a zero value under the “Period 2″ heading in my pivotted column – I don’t just want to ignore it.

One way of achieving this is by cross-joining the relevant set of category headings. Joining is one of the areas where the syntax and behaviour of LINQ is often a little different to SQL, but the behaviours provided are tremendously powerful, and can often simplify queries that would be difficult in SQL.

Cross joins in LINQ can be acheived using the SelectMany syntax. The query fragment below is an example of solving the above issue. Here we cross join nominal accounts to the current year’s accounting periods, ensuring that however we further group or filter the data, there will always be a value for any given period – perfect for populating a chart or pivotting.

return cxt.NLNominalAccounts
   .SelectMany
   (
      n=>cxt.SYSAccountingPeriods.Where(w=>w.SYSFinancialYear.YearRelativeToCurrentYear==0),
      (n,s)=>
      new
{
            NLNominalAccountID = n.NLNominalAccountID,
            PeriodYear = ((("FY" + s.SYSFinancialYear.FinancialYearStartDate.Year.ToString().Substring (2, 2)) + "P") + s.PeriodNumber.ToString().PadLeft (2, '0')),
            SYSAccountingPeriodID = s.SYSAccountingPeriodID,
      }
   )
   .GroupJoin
   (
        cxt.NLPostedNominalTrans.DefaultIfEmpty(),
        a=>new{a.NLNominalAccountID,a.SYSAccountingPeriodID},
        n=>new{n.NLNominalAccountID,n.SYSAccountingPeriodID},
        (a,n)=>new
        {
            a.NLNominalAccountID,
            a.PeriodYear,
            a.SYSAccountingPeriodID,
            CreditValue = (n.Count()==0 ? 0 : n.Sum(t=>t.GoodsValueInBaseCurrency < 0 ? -t.GoodsValueInBaseCurrency : 0) ),
            DebitValue = (n.Count()==0 ? 0 : n.Sum(t=>t.GoodsValueInBaseCurrency > 0 ? t.GoodsValueInBaseCurrency : 0) )
        }
    );