I am having trouble converting the following SQL statement to the LLBLGen generated code. Can you point me in the right direction? My research has lead me to the ResultSetFields and aggregate functions. We are using 2.0, Self servicing two classes, and c#. Any examples or assistance you can provide would be greatly appreciated.

SELECT COUNT() AS Expr1, RECIPIENT_EMAIL FROM table1 WHERE (APPROVED IS NULL) AND (GROUP_ID IS NULL) GROUP BY RECIPIENT_EMAIL HAVING (COUNT() > 1)

Thanks, Cory

The following code is not tested, but you should have something similar:

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(Table1Fields.RecepientEmail, 0, "RecepientEmail");
fields.DefineField(Table1Fields.AnyField, 1, "Count", "", AggregateFunction.CountRow);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.HavingClause = new PredicateExpression
    (new FieldCompareValuePredicate(fields[1], null, ComparisonOperator.GreaterThan, 1));

IPredicateExpression filter = new PredicateExpression();
filter.Add(Table1Fields.Approved == System.DBNull.Value);
filter.Add(Table1Fields.GroupId== System.DBNull.Value);

DataTable dynamicList = new DataTable();
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter, null, true, groupByClause, null, 0, 0);

That is great! I am trying the code out now. Is there a way to do this and populate a collection? Much of our code uses the collection.GetMulti()...

Thanks so much!

I am having trouble getting the example code to work. We are getting a "Object reference not set to an instance of an object. " error in DaoClasses\TypedListDAO.cs. Below is the error and stack trace. please help!!!

------ my .cs file code ----------

ResultsetFields fields = new ResultsetFields(2); fields.DefineField(ActsCardsFields.RecipientEmail, 0, "RecepientEmail"); fields.DefineField(ActsCardsFields.ActsId, 1, "Count", "", AggregateFunction.CountRow);

        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);
        groupByClause.HavingClause = new PredicateExpression
            (new FieldCompareValuePredicate(fields[1], null, ComparisonOperator.GreaterThan, 1));

        IPredicateExpression filter = new PredicateExpression();
        filter.Add(ActsCardsFields.Approved == System.DBNull.Value);
        filter.Add(ActsCardsFields.GroupId == System.DBNull.Value);

        DataTable dynamicList = new DataTable();
        TypedListDAO dao = new TypedListDAO();
        dao.GetMultiAsDataTable(fields, dynamicList, 0, null, filter, null, true, groupByClause, null, 0, 0);

--------- error stack trace ------------

Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

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: } Line 51:

Source File: C:\VS2005Projects\ACTS\ActsDAL\DaoClasses\TypedListDAO.cs Line: 49

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.] SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateParameter(IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ParameterDirection direction, Object valueToSet) +45 SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareValuePredicate.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) +363 SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Int32& uniqueMarker, Boolean inHavingClause) +579 SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection.ToQueryText(Int32& uniqueMarker, Boolean ignoreExpressions) +534 SD.LLBLGen.Pro.ORMSupportClasses.GroupByCollection.ToQueryText(Int32& uniqueMarker) +31 SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendGroupByClause(StringBuilder queryText, IGroupByCollection groupByClause, IDbCommand cmd) +166 SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) +3029 SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) +474 SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) +183 SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFields selectList, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) +99 SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.CreateQueryFromElements(ITransaction transactionToUse, IEntityFields fields, IPredicate filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) +438 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) +147 ActsDAL.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:\VS2005Projects\ACTS\ActsDAL\DaoClasses\TypedListDAO.cs:49 admin_leaveApproval.Page_Load(Object sender, EventArgs e) in c:\VS2005Projects\ACTS\Acts\acts\admin\adminLeaveApproval.aspx.cs:55 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68 System.Web.UI.Control.OnLoad(EventArgs e) +88 System.Web.UI.Control.LoadRecursive() +74 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3036

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

never mind...

Changed: groupByClause.HavingClause = new PredicateExpression (new FieldCompareValuePredicate(fields[1], null, ComparisonOperator.GreaterThan, 1));

to:

groupByClause.HavingClause = new PredicateExpression (new FieldCompareValuePredicate(fields[1], ComparisonOperator.GreaterThan, 1));

(removed null to use different overloaded method)