May I have PredicateExpression for where condition

select * from xxx where month(fieldname) = 5 and year(fieldname2) = 2005

month and year are functions (db specific) which are not supported at the moment through predicates.

You can solve this by implementing IExpression in your own class which simply in ToQueryText wraps the field name with 'month' or 'year' or whatever you set as a flag in the constructor of your IExpression implementation. You can also derive that class from Expression and override ToQueryText. See for details how to create the field's name the Expression sourcecode in the Runtime library sourcecode.

Otis wrote:

month and year are functions (db specific) which are not supported at the moment through predicates.

You can solve this by implementing IExpression in your own class which simply in ToQueryText wraps the field name with 'month' or 'year' or whatever you set as a flag in the constructor of your IExpression implementation. You can also derive that class from Expression and override ToQueryText. See for details how to create the field's name the Expression sourcecode in the Runtime library sourcecode.

May I have detail/Example for create IExpression override ToQueryText .

Hi there,

I'm trying to apply a function to an expression.

Here is my attempt...

[Serializable]
    public class FunctionExpression:Expression
    {
        #region class member declarations
        private string _functionToApply;
        private IExpression _expression;
        #endregion

        public FunctionExpression(IExpression expression, string functionToApply) : base()
        {
            _expression = expression;
            _functionToApply = functionToApply;
        }
    
        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(_functionToApply.Length>0)
            {
                return string.Format("{0}({1})", _functionToApply, _expression.ToQueryText(ref uniqueMarker, inHavingClause));
            }
            else
            {
                return _expression.ToQueryText(ref uniqueMarker, inHavingClause);
            }
        }
    
        #region properties
        public string FunctionToApply
        {
            get { return _functionToApply;}
            set { _functionToApply = value;}
        }
        #endregion
    }

Here is the usage...

IExpression xOperand = new Expression(UkPostcodesFields.X, ExOp.Sub, entity.X);
                IExpression yOperand = new Expression(UkPostcodesFields.Y, ExOp.Sub, entity.Y);

                IExpression x2Operand = new FunctionExpression(xOperand, "SQUARE");
                IExpression y2Operand = new FunctionExpression(yOperand, "SQUARE");

                IExpression c2Operand = new Expression(x2Operand, ExOp.Add, y2Operand);

                EntityField2 field = UkPostcodesFields.X.SetExpression(c2Operand);

                IPredicate filter = field <= distanceInMeters2;

                bucket.PredicateExpression.Add(filter);

...and I'm getting the following error...

'DatabaseSpecificCreator object not set. Cannot create query part. '

...when the following line is run..

return string.Format("{0}({1})", _functionToApply, _expression.ToQueryText(ref uniqueMarker, inHavingClause));

How do I correct this code?

This exception is shown when you use an Expression and its DatabaseSpecificCreator property is not set to a valid value.

Please check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4845

I've allready make this Expression, but I don't use MONTH and YEAR but DATEPART() so I've more possibilities

Here is the code :

using System;
using System.Collections;
using System.Text;
using System.Data;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace HRAccent.BusinessLayer.Llblgen
{
    /// <summary>
    /// Different type accepted for day part expression
    /// </summary>
    public enum DatePartType 
    {
        Year,
        Quarter, 
        Month, 
        DayOfYear, 
        Day, 
        Week, 
        Weekday, 
        Hour,
        Minute, 
        Second, 
        Millisecond,
    }
    /// <summary>
    /// Expression class to use a "DayPart" function in a SQL query
    /// applied to fields in a select list, in update queries or in field predicates.
    /// <br/>
    /// Valid expressions:
    /// <list type="ul">
    /// <item>DatePartType, Field</item>
    /// <item>DatePartType, Value</item>
    /// </list>
    /// Use one of the constructors to create the particular expression object.
    /// </summary>
    /// <remarks>Values are transformed into parameters. The type of the parameter is determined of the field in the expression.</remarks>
    [Serializable]
    public class DatePartExpression : IExpression
    {
        #region Class Member Declarations
        private IDbSpecificCreator  _creator;
        private IExpressionElement  _leftOperand, _rightOperand;
        private ExOp                _operator;
        private DatePartType        _type;

        [NonSerialized]
        private ArrayList           _parameters;
        #endregion


        #region Constructors
        /// <summary>
        /// CTor
        /// </summary>
        /// <remarks>Empty constructor, do not use, use one of the constructor overloads to create an expression instance.</remarks>
        public DatePartExpression()
        {
            InitClass();
            _leftOperand = null;
            _rightOperand = null;
            _operator = ExOp.None;
        }

        
        /// <summary>
        /// CTor with datetime value
        /// </summary>
        /// <param name="type">Type of day part</param>
        /// <param name="valueToUse">Field of value</param>
//      public DatePartExpression(DatePartType type, ExOp operatorToUse, IExpression rightOperand) 
//      {
//          InitClass();
//          _rightOperand   = new ExpressionElement(ExpressionElementType.Expression, rightOperand);
//          _operator       = operatorToUse;
//          _type           = type;
//      }

        /// <summary>
        /// CTor with field value
        /// </summary>
        /// <param name="type">Type of day part</param>
        /// <param name="valueToUse">Field of value</param>
//      public DatePartExpression(DatePartType type, ExOp operatorToUse, object rightOperand) 
//      {
//          InitClass();
//          _rightOperand   = new ExpressionElement(ExpressionElementType.Value, rightOperand);
//          _operator       = operatorToUse;
//          _type           = type;
//      }

        /// <summary>
        /// CTor for (expression) operator field expressions. 
        /// Adapter specific
        /// </summary>
        /// <param name="leftOperand">the left operand, which is an IExpression implementation</param>
        /// <param name="operatorToUse">operator to use in this expression</param>
        /// <param name="rightOperand">the right operand, which is a field</param>
        public DatePartExpression(DatePartType type, ExOp operatorToUse, IEntityField2 rightOperand)
        {
            InitClass();
            _leftOperand    = new ExpressionFieldElement(ExpressionElementType.Field, rightOperand, null);
            _rightOperand   = new ExpressionFieldElement(ExpressionElementType.Field, rightOperand, null);
            _operator       = operatorToUse;
            _type           = type;
        }
        #endregion

        private string ToDatePartText(DatePartType type) 
        {
            string fieldName;
            switch (type) 
            {
                case DatePartType.Day           :
                    fieldName = "dd";
                    break;
                case DatePartType.DayOfYear     :
                    fieldName = "dd";
                    break;
                case DatePartType.Hour          :
                    fieldName = "hh";
                    break;
                case DatePartType.Millisecond   :
                    fieldName = "ms";
                    break;
                case DatePartType.Minute        :
                    fieldName = "mi";
                    break;
                case DatePartType.Month         :
                    fieldName = "mm";
                    break;
                case DatePartType.Quarter       :
                    fieldName = "qq";
                    break;
                case DatePartType.Second        :
                    fieldName = "ss";
                    break;
                case DatePartType.Week          :
                    fieldName = "wk";
                    break;
                case DatePartType.Weekday       :
                    fieldName = "dw";
                    break;
                case DatePartType.Year          :
                    fieldName = "yyyy";
                    break;
                default:
                    throw new ArgumentException("DatePartType not handled : " + type.ToString());
            }

            return fieldName;
        }

        /// <summary>
        /// Retrieves a ready to use text representation of the contained expression. 
        /// </summary>
        /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
        /// making sure the parameter is unique in the expression and also in the expression(s) containing the expression.</param>
        /// <returns>The contained expression in textual format.</returns>
        /// <exception cref="System.ApplicationException">When IExpression.DatabaseSpecificCreator is not set to a valid value.</exception>
        public string ToQueryText(ref int uniqueMarker)
        {
            return ToQueryText(ref uniqueMarker, false);
        }

        /// <summary>
        /// Retrieves a ready to use text representation of the contained expression. 
        /// </summary>
        /// <param name="uniqueMarker">int counter which is appended to every parameter. The refcounter is increased by every parameter creation,
        /// making sure the parameter is unique in the expression and also in the expression(s) containing the expression.</param>
        /// <param name="inHavingClause">if set to true, it will allow aggregate functions to be applied to fields.</param>
        /// <returns>The contained expression in textual format.</returns>
        /// <exception cref="System.ApplicationException">When IExpression.DatabaseSpecificCreator is not set to a valid value.</exception>
        public virtual string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(_creator==null)
            {
                throw new System.ApplicationException("DatabaseSpecificCreator object not set. Cannot create query part.");
            }

            StringBuilder queryText = new StringBuilder(128);
            _parameters.Clear();


            if (this.RightOperand != null) 
            {
                string leftOp   = this.ToDatePartText(this._type);
                string rightOp  = this.OperandToText(ref queryText, this._rightOperand, ref uniqueMarker, false, inHavingClause);
                
                queryText.AppendFormat(null, "DATEPART({0}, {1})", leftOp, rightOp);
            }

            return queryText.ToString();
        }


        /// <summary>
        /// Converts the passed in operand to text, appended to queryText. parameters created are added to _parameters.
        /// </summary>
        /// <param name="queryText"></param>
        /// <param name="operand"></param>
        /// <param name="uniqueMarker"></param>
        /// <param name="isLeftOperand"></param>
        /// <param name="inHavingClause"></param>
        private string OperandToText(ref StringBuilder queryText, IExpressionElement operand, ref int uniqueMarker, bool isLeftOperand, bool inHavingClause)
        {
            IExpressionFieldElement fieldElement = null;

            switch(operand.Type)
            {
                case ExpressionElementType.Value:
                    IDataParameter parameter = _creator.CreateParameter("LO" + operand.GetHashCode().ToString(), ParameterDirection.Input, operand.Contents);
                    _parameters.Add(parameter);
                    uniqueMarker++;
                    parameter.Value = operand.Contents;
                    parameter.ParameterName += uniqueMarker.ToString();
                    return parameter.ParameterName;
                case ExpressionElementType.Field:
                    fieldElement = (ExpressionFieldElement)operand;
                    IEntityFieldCore fieldCore = (IEntityFieldCore)fieldElement.Contents;
                    if(fieldCore.ExpressionToApply!=null)
                    {
                        // add parameters to this expression's collection.
                        _parameters.AddRange(fieldCore.ExpressionToApply.Parameters);
                    }
                    return string.Format("{0}", _creator.CreateFieldName(fieldCore, fieldElement.PersistenceInfo, fieldCore.Name, fieldCore.ObjectAlias, ref uniqueMarker, inHavingClause));
                case ExpressionElementType.Expression:
                    IExpression expressionToConvert = (IExpression)operand.Contents;
                    // pass on the name creator object
                    expressionToConvert.DatabaseSpecificCreator = _creator;
                    _parameters.AddRange(expressionToConvert.Parameters);
                    return string.Format("({0})", expressionToConvert.ToQueryText(ref uniqueMarker, inHavingClause));
            }
            throw new ArgumentException("Invalid ExpressionElementType:" + operand.Type.ToString());
        }


        /// <summary>
        /// inits members
        /// </summary>
        private void InitClass()
        {
            _parameters = new ArrayList();
            _creator = null;
        }


        #region Class Property Declarations
        /// <summary>
        /// The list of parameters created when the Expression was translated to text usable in a query. Only valid after a succesful call to ToQueryText
        /// </summary>
        public ArrayList Parameters 
        {
            get { return _parameters;}
        }
        
        /// <summary>
        /// Object which will be used to create valid parameter objects, field names, including prefix/postfix characters, 
        /// and conversion routines, and field names, including prefix/postfix characters. 
        /// Uses the strategy pattern so the generic code can work with more than one target database.
        /// </summary>
        public IDbSpecificCreator DatabaseSpecificCreator 
        {
            get { return _creator;}
            set {_creator = value;}
        }

        /// <summary>
        /// Gets the left expression operand. Set by the constructor used.
        /// </summary>
        public IExpressionElement LeftOperand 
        {
            get { return _leftOperand;}
        }

        /// <summary>
        /// Gets the right expression operand. Set by the constructor used.
        /// Can be null
        /// </summary>
        public IExpressionElement RightOperand 
        {
            get { return _rightOperand;}
        }
        /// <summary>
        /// Gets the operator of the expression. Not valid (ExOp.None) if RightOperand is null. Set by the constructor used.
        /// </summary>
        public ExOp Operator 
        {
            get { return _operator;}
        }
        #endregion

    }
}

Cool

Thanks Fabrice for the code, I'm sure many will find it very useful.

You're welcome I'm not sure the code is very nice, but I've make some nunit and it work fine since a few month in out application (but it's not used a lot) If you've any comments/bug fix it'll be nice to share, thank you

I got mine working with the following...

[Serializable]
    public class FunctionExpression : Expression
    {
        private string _functionToApply;

        public FunctionExpression(IExpression expression, string functionToApply) : base(expression, ExOp.None, (object)null)
        {
            _functionToApply = functionToApply;
        }
    
        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(_functionToApply.Length>0)
            {
                return string.Format("{0}({1})", _functionToApply, base.ToQueryText(ref uniqueMarker, inHavingClause));
            }
            else
            {
                return base.ToQueryText(ref uniqueMarker, inHavingClause);
            }
        }
    }

Its practically the same as Otis's inital example except I passed an expression down to the base class instead of a field.

Ian wrote:

I got mine working with the following...

[Serializable]
    public class FunctionExpression : Expression
    {
        private string _functionToApply;

        public FunctionExpression(IExpression expression, string functionToApply) : base(expression, ExOp.None, (object)null)
        {
            _functionToApply = functionToApply;
        }
    
        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(_functionToApply.Length>0)
            {
                return string.Format("{0}({1})", _functionToApply, base.ToQueryText(ref uniqueMarker, inHavingClause));
            }
            else
            {
                return base.ToQueryText(ref uniqueMarker, inHavingClause);
            }
        }
    }

Its practically the same as Otis's inital example except I passed an expression down to the base class instead of a field.

My coworker has created a very similar expression wrapper, with the exception that it is manipulating a varchar field using some string fuctions. In this case, he would also like to apply the expression to the sort clause so the sort respects the manipulation performed. Looking at the SortExpression Clause syntax, I don't see a way to sort with the user defined expression in place. Am I missing something? Due to the complexity of the view being queried (and more specifically the filter conditions that can be applied to it) it would be preferable to not wrap within a stored procedure or nest views.

Nevermind, we have figured it out

Hi:

I have been trying to figure this one out, but with no luck. Here is the code I have implemented (Self-Servicing, v1.0.2005.1, 03/02/2006)...

    public class MonthExpression : Expression
    {
        private string monthFunction;

        public MonthExpression() : base()
        {
            this.monthFunction = "MONTH";
        }

        public MonthExpression(IEntityField field) : base(field)
        {
            this.monthFunction = "MONTH";
        }

        public MonthExpression(IEntityField2 field) : base(field)
        {
            this.monthFunction = "MONTH";
        }
    
    
        public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
        {
            if(monthFunction.Length > 0)
            {
                return string.Format("{0}({1})", monthFunction, base.ToQueryText(ref uniqueMarker, inHavingClause));
            }
            else
            {
                return base.ToQueryText(ref uniqueMarker, inHavingClause);
            }
        }
    }

Here is the code that invokes the above class...

            ResultsetFields articleFields = new ResultsetFields(3);
            articleFields.DefineField(ArticleFieldIndex.CreateDate, 0, "CreateDateMonth");
            articleFields.DefineField(ArticleFieldIndex.CreateDate, 1, "CreateDateYear");
            articleFields.DefineField(ArticleFieldIndex.ArticleTopicId, 2, "ArticleTopicId");

            IExpression monthExpression = new CustomExpressions.MonthExpression(articleFields["CreateDateMonth"]);

            IPredicateExpression havingFilter = new PredicateExpression();
            havingFilter.Add(new FieldCompareValuePredicate(articleFields["TopicId"], ComparisonOperator.Equal, this.TopicId));

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(articleFields["CreateDateMonth"]);
            groupByClause.Add(articleFields["CreateDateYear"]);
            groupByClause.HavingClause = havingFilter;

            base.GroupByClause = groupByClause;
            base.Fields = articleFields;

Which gets called by...

        public override void FillDataTable()
        {
            QueryFactory();
            base.FillDataTable();

            TypedListDAO dao = new TypedListDAO();
            
            dao.GetMultiAsDataTable(base.Fields, this.Table, 0, null, null, this.articleArchiveListTypedList.BuildRelationSet(), true, base.GroupByClause, null, 0, 0);

        }

Here is the first exception I get...

 Line 1: Incorrect syntax near ')'.
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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ')'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Line 1: Incorrect syntax near ')'.]
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IEntityFields fieldsToReturn)
   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)

But then, if I add this instead...

            IExpression monthExpression = new CustomExpressions.MonthExpression(articleFields["CreateDateMonth"]);
            articleFields["CreateDateMonth"].ExpressionToApply = monthExpression;

... my application doesn't even step in, and I get the following exception...

 Exception of type System.StackOverflowException was thrown.
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.StackOverflowException: Exception of type System.StackOverflowException was thrown.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[StackOverflowException: Exception of type System.StackOverflowException was thrown.]

I've even tried the fancy DatePart example posted above, except I switched the CTor argument to an EntityField, but I get the same Stack Overflow.

Any clues why this is bombing?

Thanks!

Isz

(sorry for the late reply, it had fallen outside the active threads queue)

You get a stackoverflow because the field containing the expression is also an argument in the expression which then gets re-evaluated again etc. etc.

You should do: articleFields[0].SetExpression(new MonthExpression(ArticleFields.CreateDate));

this sets the field at position 0's expression property to a new expression of type MonthExpression and utilizes a new field object inside the expression which then will make the DQE to generate the fieldname as the argument to the function.

Wow! Thats awesome... I think I almost have this going. Is it possible to add expressions to the GroupByCollection? For example, I have a t-sql that does this:

select MONTH(article.create_date) as [Month], YEAR(article.create_date) as [Year], Count(article.article_topic_id) as [Total]
from topic
inner join article on topic.topic_id = article.article_topic_id
group by MONTH(article.create_date), YEAR(article.create_date), article.article_topic_id
Having article.article_topic_id = 47

Here is the current output I am getting from the DQE:

SELECT 
MONTH([dbo].[article].[create_date]) AS [CreateDateMonth], 
YEAR([dbo].[article].[create_date]) AS [CreateDateYear], 
[dbo].[article].[article_topic_ID] AS [ArticleTopicId] 
FROM ( [dbo].[topic]  
INNER JOIN [dbo].[article]  ON  [dbo].[topic].[topic_ID]=[dbo].[article].[article_topic_ID]) 
GROUP BY [dbo].[article].[create_date], [dbo].[article].[create_date], [dbo].[article].[article_topic_ID] 
HAVING ( [dbo].[article].[article_topic_ID] = 47)
--  Parameter: @ArticleTopicId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 47.

Here is the code that calls the above:

            ResultsetFields articleFields = new ResultsetFields(3);
            articleFields.DefineField(ArticleFieldIndex.CreateDate, 0, "CreateDateMonth");
            articleFields.DefineField(ArticleFieldIndex.CreateDate, 1, "CreateDateYear");
            articleFields.DefineField(ArticleFieldIndex.ArticleTopicId, 2, "ArticleTopicId");

            articleFields[0].SetExpression(new CustomExpressions.MonthExpression(ArticleFields.CreateDate));
            articleFields[1].SetExpression(new CustomExpressions.YearExpression(ArticleFields.CreateDate));
    
            IPredicateExpression havingFilter = new PredicateExpression();
            havingFilter.Add(new FieldCompareValuePredicate(articleFields["ArticleTopicId"], ComparisonOperator.Equal, this.TopicId));

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(articleFields["CreateDateMonth"]);
            groupByClause.Add(articleFields["CreateDateYear"]);
            groupByClause.Add(articleFields["ArticleTopicId"]);
            groupByClause.HavingClause = havingFilter;
            
            base.GroupByClause = groupByClause;
            base.Fields = articleFields;

I suppose I could leave out the Year expression and manually group by that in my code, but if it is included then my results are off as it is grouping more on a date value rather than the int month and year..

I didn't see anyway to add expressions to the groupby clause, but I could be missing something.

Thanks!

If an expression is applied to a field using the property "ExpressionToApply" ot the method "SetExpression", then this expression should have been reflected in the Group By clause.

So if this is not happenning to you, maybe you are using an old version of the runtime Libraries.

Please specify the runtimeLibrary version used.

Also check the folowing thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3549

SD.LLBLGen.Pro.DQE.SqlServer.NET11.dll = 1.0.20051.60314 SD.LLBLGen.Pro.ORMSupportClasses.NET11.dll = 1.0.20051.60317

Are these good to use?

The ouput I am getting is definately:

SELECT
MONTH([dbo].[article].[create_date]) AS [CreateDateMonth],
YEAR([dbo].[article].[create_date]) AS [CreateDateYear],
[dbo].[article].[article_topic_ID] AS [ArticleTopicId]
FROM ( [dbo].[topic]
INNER JOIN [dbo].[article] ON [dbo].[topic].[topic_ID]=[dbo].[article].[article_topic_ID])
GROUP BY [dbo].[article].[create_date], [dbo].[article].[create_date], [dbo].[article].[article_topic_ID]
HAVING ( [dbo].[article].[article_topic_ID] = 47)
--  Parameter: @ArticleTopicId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 47.

With the following expression:

 ResultsetFields articleFields = new ResultsetFields(3);
            articleFields.DefineField(ArticleFieldIndex.CreateDate, 0, "CreateDateMonth");
            articleFields.DefineField(ArticleFieldIndex.CreateDate, 1, "CreateDateYear");
            articleFields.DefineField(ArticleFieldIndex.ArticleTopicId, 2, "ArticleTopicId");

            articleFields[0].SetExpression(new CustomExpressions.MonthExpression(ArticleFields.CreateDate));
            articleFields[1].SetExpression(new CustomExpressions.YearExpression(ArticleFields.CreateDate));
    
            IPredicateExpression havingFilter = new PredicateExpression();
            havingFilter.Add(new FieldCompareValuePredicate(articleFields["ArticleTopicId"], ComparisonOperator.Equal, this.TopicId));

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(articleFields["CreateDateMonth"]);
            groupByClause.Add(articleFields["CreateDateYear"]);
            groupByClause.Add(articleFields["ArticleTopicId"]);
            groupByClause.HavingClause = havingFilter;

Also, the aggregate Count(article.article_topic_id) as [Total] that I listed in my previous query is not totally essential, but I appreciate the link referral... I will probably implement that once I get this working (baby steps)

SD.LLBLGen.Pro.DQE.SqlServer.NET11.dll = 1.0.20051.60314 SD.LLBLGen.Pro.ORMSupportClasses.NET11.dll = 1.0.20051.60317 Are these good to use?

They are rather old. Please try to download and use the latest version of the runtimeLibraries.

Ok... I updated, now i have...

SD.LLBLGen.Pro.DQE.SqlServer.NET11.dll = 1.0.20051.60516 SD.LLBLGen.Pro.ORMSupportClasses.NET11.dll = 1.0.20051.60628

The expression is still not being passed to the group by clause. Any ideas?

This is going to be a dissapointment. I appologize for not correctly reading the previous posts as that would have saved us all a lot of time.

In v1.0.200x.y, expressions on groupby fields are ignored. This was an unfortunate design decision, however as changing it in the middle of a version would create behavior change, we postponed the change till v2.

You can build it in into the DQE code, but it's not straight forward as in: a couple of lines. In the base class of the DynamicQueryEngine is located the routine AppendGroupByClause() which doesn't pass on the expression of the fields to the query.

You can alter this, and use a routine like this:

public virtual string ToQueryText( ref int uniqueMarker, bool ignoreExpressions )
{
    StringBuilder queryText = new StringBuilder(256);
    _parameters = new ArrayList();

    for( int i = 0; i < this.Count; i++ )
    {
        if( i > 0 )
        {
            queryText.Append( ", " );
        }
        IEntityFieldCore currentField = this[i];
        string toGroupOn = string.Empty;
        if(( currentField.ExpressionToApply != null ) && !ignoreExpressions)
        {
            currentField.ExpressionToApply.DatabaseSpecificCreator = _databaseSpecificCreator;
            toGroupOn = currentField.ExpressionToApply.ToQueryText( ref uniqueMarker, false );
            _parameters.AddRange( currentField.ExpressionToApply.Parameters );
        }
        else
        {
            toGroupOn = _databaseSpecificCreator.CreateFieldName(
                GetFieldPersistenceInfo( i ), currentField.Name, currentField.ObjectAlias,
                currentField.ContainingObjectName );
        }

        queryText.Append( toGroupOn );
    }

    if( (_havingClause != null) && (_havingClause.Count > 0) )
    {
        _havingClause.DatabaseSpecificCreator = _databaseSpecificCreator;
        queryText.AppendFormat( null, " HAVING {0}", _havingClause.ToQueryText( ref uniqueMarker, true ) );
        _parameters.AddRange( _havingClause.Parameters );
    }

    return queryText.ToString();
}

which is the toquerytext of GroupByCollection in v2. This code obviously can't be ported 1:1 to v1 code but it gives you an idea what should be done. One thing you have to take care of is passing the parameters of the expression back to the CreateSelectDQ routine in the DynamicQueryEngine class of the DQE you're using. You can do the override in that class.

No dissappointment... I am in this boat to learn, and llbl is one of the best platforms I've ever worked with. I'll try the implmentation you list below, but may also be able to talk our director into upgrading to 2.0 soon, which I think is gonna happen soon anyway.