Hi, I'm stuck with this one. Could I have some help please? I'm using SQL Server 2005, .Net 2, LLBLGen v2.6 -Oct 8 2008, and SelfServicing.

This is the SQL I am trying to replicate:

SELECT 
     M1.DebtorGroupId,
    (SELECT COUNT(*) FROM MatchingLog M2 
          WHERE M1.DebtorGroupId = M2.DebtorGroupId
          AND M2.DateStamp BETWEEN @StartDate AND @EndDate
          AND M2.MatchingLogTypeId = 1) AS DebtorsAdded,
    (SELECT COUNT(*) FROM MatchingLog M2 
          WHERE M1.DebtorGroupId = M2.DebtorGroupId
          AND M2.DateStamp BETWEEN @StartDate AND @EndDate
          AND M2.MatchingLogTypeId = 2) AS DebtorsRemoved,
    (SELECT COUNT(*) FROM DebtorDetails DD
          WHERE M1.DebtorGroupId = DD.DebtorGroupId) AS TotalDebtors
FROM MatchingLog M1
WHERE M1.DateStamp BETWEEN @StartDate AND @EndDate
GROUP BY M1.DebtorGroupId

Key points about the schema are DebtorDetails.DebtorGroupId has m:1 to DebtorGroup.DebtorGroupId MatchingLog tracks adds/removes of DebtorDetails to/from DebtorGroups (using the TypeId=1/2)

Here is the code I've come up with:

// get a summary of additions-to and removals-from groups during the date range
ResultsetFields fieldsSummary = new ResultsetFields(4);
fieldsSummary.DefineField(MatchingLogFields.DebtorGroupId, 0, "M1");

// sub-query to get number of debtors added to this group in the date range
PredicateExpression debtorsAddedFilter = new PredicateExpression();
debtorsAddedFilter.Add(MatchingLogFields.DebtorGroupId.SetObjectAlias("M2") == MatchingLogFields.DebtorGroupId);
debtorsAddedFilter.Add(MatchingLogFields.MatchingLogTypeId == 1);
debtorsAddedFilter.Add(new FieldBetweenPredicate(MatchingLogFields.DateStamp, StartDate, EndDate));
fieldsSummary.DefineField(new EntityField("DebtorsAdded",
    new ScalarQueryExpression(
        MatchingLogFields.MatchingLogId.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("M2"),
        debtorsAddedFilter)), 1);

// sub-query to get number of debtors removed from this group in the date range
PredicateExpression debtorsRemovedFilter = new PredicateExpression();
debtorsRemovedFilter.Add(MatchingLogFields.DebtorGroupId.SetObjectAlias("M3") == MatchingLogFields.DebtorGroupId);
debtorsRemovedFilter.Add(MatchingLogFields.MatchingLogTypeId == 2);
debtorsRemovedFilter.Add(new FieldBetweenPredicate(MatchingLogFields.DateStamp, StartDate, EndDate));
fieldsSummary.DefineField(new EntityField("DebtorsRemoved",
    new ScalarQueryExpression(
        MatchingLogFields.MatchingLogId.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("M3"),
        debtorsRemovedFilter)), 2);

// sub-query to get number of debtors in the group as at now
fieldsSummary.DefineField(new EntityField("TotalDebtors",
    new ScalarQueryExpression(
        DebtorDetailsFields.DebtorDetailsId.SetAggregateFunction(AggregateFunction.Count),
        (MatchingLogFields.DebtorGroupId == DebtorDetailsFields.DebtorGroupId))), 3);

// where
PredicateExpression filterSummary = new PredicateExpression();
filterSummary.Add(new FieldBetweenPredicate(BalanceLogFields.DateStamp, StartDate, EndDate));

// group by
GroupByCollection groupBySummary = new GroupByCollection(MatchingLogFields.DebtorGroupId);

DataTable dtSummary = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fieldsSummary, dtSummary, 0, null, filterSummary, null, true, groupBySummary, null, 0, 0);

Problem is, it throws a Null Reference Exception in GetMultiAsDataTable() in DaoClasses\TypedListDAO.cs:

Line 47:        public bool GetMultiAsDataTable(IEntityFields fieldsToReturn, DataTable tableToFill, long maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, bool allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, int pageNumber, int pageSize)
Line 48:        {
Line 49:            return base.PerformGetMultiAsDataTableAction(fieldsToReturn, tableToFill, maxNumberOfItemsToReturn, sortClauses, selectFilter, relations, allowDuplicates, groupByClause, transactionToUse, pageNumber, pageSize);
Line 50:        }

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IEntityFields fieldsToReturn) +411
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize) +157
   XXXX.ORM.DaoClasses.TypedListDAO.GetMultiAsDataTable(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize) in C:\Source\ORM\DaoClasses\TypedListDAO.cs:49
   XXXX.BusinessServices.Reports.AdminReports.GroupBalanceExceptionRecords2(DateTime StartDate, DateTime EndDate, Int32 Variance, Boolean CompaniesOnly) in C:\Source\XXXX.BusinessServices\Reports\AdminReports.cs:303
   XXXX.Web.GroupMembershipMovementsReport.ShowReport(DateTime startDate, DateTime endDate, Int32 percentageVariance, Boolean companiesOnly) in C:\Source\Web\GroupMembershipMovementsReport.aspx.cs:141
   XXXX.Web.GroupMembershipMovementsReport.btnRun_Click(Object sender, EventArgs e) in C:\Source\Web\GroupMembershipMovementsReport.aspx.cs:109
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Can anyone see what I'm doing wrong? Thanks very much

Seems like an alias problem. Please post the generated sql for that fetch.

And runtime lib build nr. llblgen pro version etc. (see guidelines thread of this forum!)

I located the problem and perhaps unsurprisingly it was a typo error:

in the outer predicate code:

// where
PredicateExpression filterSummary = new PredicateExpression();
filterSummary.Add(new FieldBetweenPredicate(BalanceLogFields.DateStamp, StartDate, EndDate));

I made reference to **BalanceLogFields **where it should have been MatchingLogFields.

oops.

Thanks for taking to time to read my post; sorry to waste your time

There were a couple unrelated errors in my code that I have since fixed too: notably I had forgotten to use .SetObjectAlias consistently, so :

            // sub-query to get number of debtors added to this group in the date range
            PredicateExpression debtorsAddedFilter = new PredicateExpression();
            debtorsAddedFilter.Add(MatchingLogFields.DebtorGroupId.SetObjectAlias("M2") == MatchingLogFields.DebtorGroupId);
            debtorsAddedFilter.Add(MatchingLogFields.MatchingLogTypeId == 1);
            debtorsAddedFilter.Add(new FieldBetweenPredicate(MatchingLogFields.DateStamp, StartDate, EndDate));
            fieldsSummary.DefineField(new EntityField("DebtorsAdded",
                new ScalarQueryExpression(
                    MatchingLogFields.MatchingLogId.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("M2"),
                    debtorsAddedFilter)), 1);

should have been:

            // sub-query to get number of debtors added to this group in the date range
            PredicateExpression debtorsAddedFilter = new PredicateExpression();
            debtorsAddedFilter.Add(MatchingLogFields.DebtorGroupId.SetObjectAlias("M2") == MatchingLogFields.DebtorGroupId);
            debtorsAddedFilter.Add(MatchingLogFields.MatchingLogTypeId.SetObjectAlias("M2") == 1);
            debtorsAddedFilter.Add(new FieldBetweenPredicate(MatchingLogFields.DateStamp.SetObjectAlias("M2"), StartDate, EndDate));
            fieldsSummary.DefineField(new EntityField("DebtorsAdded",
                new ScalarQueryExpression(
                    MatchingLogFields.MatchingLogId.SetAggregateFunction(AggregateFunction.Count).SetObjectAlias("M2"),
                    debtorsAddedFilter)), 1);

It's working just fine now though. Thanks.

slade52 wrote:

Thanks for taking to time to read my post; sorry to waste your time

No worries, we are here to help you Good to know it's working now.