Hi

I have this SQLquery that I would like to call using LLBLgen Dynamic List, is this possibly or should I create and Stored Procedure?

SELECT Len(TBLsite_user.Firstname) as CharsCount, count( DISTINCT TBLsite_user.MobilePhone ) As MobileRecipientsCount FROM TBLsite_user GROUP BY len(TBLsite_user.Firstname)

If this is possibly I would also like to know how to add an ISNull around it, like this: SELECT IsNull(Len(TBLsite_user.Firstname),0) as CharsCount

Regards,

Jacob Andersen

MCB www.mcb.dk Denmark

No dice at this point, sorry. You can inject SQL function text in predicate constructions, but not in the select list.

Jeff...

check this thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

you can apply that to a field in a dynamic list. So you can write a custom IExpression implementation which emits Len() or whatever you want

Thanks, hope you can help me with the implementation.

As the method ToQueryText is not virtual I used the new operator insted

public new string ToQueryText(ref int uniqueMarker, bool inHavingClause) {

Now to my code.

      ResultsetFields fields = new ResultsetFields(1);

    
      fields.DefineField( BenefitArchiveContactFieldIndex.Firstname,0,"charsCount");
    
    
      IExpression exp = new FunctionExpression( 
        EntityFieldFactory.Create(BenefitArchiveContactFieldIndex.Firstname),"len");
      fields[0].ExpressionToApply=exp;

      IGroupByCollection groupByClause = new GroupByCollection();
      groupByClause.Add(fields[0]);
    

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

I hoped that this would make the following query:

SELECT   LEN(firstname) AS charsCount
FROM         TBLbenefitArchiveContact
GROUP BY LEN(firstname)

What I get is a list of grouped firstname's, and not their length's.

Any suggestions

jacob wrote:

Thanks, hope you can help me with the implementation.

As the method ToQueryText is not virtual I used the new operator insted

public new string ToQueryText(ref int uniqueMarker, bool inHavingClause) {

ToQueryText is virtual, there are 2 overloads, you should override the one you now re-defined . If you use 'new' your code will never be called, because you then won't use polymorphism.

Now to my code.

      ResultsetFields fields = new ResultsetFields(1);

    
      fields.DefineField( BenefitArchiveContactFieldIndex.Firstname,0,"charsCount");
    
    
      IExpression exp = new FunctionExpression( 
        EntityFieldFactory.Create(BenefitArchiveContactFieldIndex.Firstname),"len");
      fields[0].ExpressionToApply=exp;

      IGroupByCollection groupByClause = new GroupByCollection();
      groupByClause.Add(fields[0]);
    

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

I hoped that this would make the following query:

SELECT   LEN(firstname) AS charsCount
FROM         TBLbenefitArchiveContact
GROUP BY LEN(firstname)

Your code looks ok. Could you please paste some code from FunctionExpression because that apparently isn't working correctly.

Looks like the method wasn't virtual in earlier versions, I used the one from May. Just upgraded and changed frem "new" to "override".

It looks like the Expression only get applied to the selectfields and not the group by statement.

My Code:

using System;
using SD.LLBLGen.Pro.ORMSupportClasses;


namespace LLBLgenExt {

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

    public FunctionExpression():base() {
      _functionToApply = string.Empty;
    }

    /// Selfservicing constructor.All other constructors are hidden, we only work on a single field.
    public FunctionExpression(IEntityField field, string functionToApply) : base(field) {
      _functionToApply = functionToApply;
    }

    /// Adapter constructor. All other constructors are hidden, we only work on a single field.
    public FunctionExpression(IEntityField2 field, string functionToApply) : base(field) {
      _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);
      }
    }
    
    
    #region properties
    public string FunctionToApply {
      get { return _functionToApply;}
      set { _functionToApply = value;}
    }
    #endregion
  }
}

And implementation:

    public DataTable GetArchiveContactCharCount( int companyGuid, int[] archiveGroupGuids){
      ResultsetFields fields = new ResultsetFields(1);

      fields.DefineField( BenefitArchiveContactFieldIndex.Firstname,0,"charsCount");
    
    

      IGroupByCollection groupByClause = new GroupByCollection();
      groupByClause.Add(fields[0]);
    
      IExpression exp = new FunctionExpression( 
        EntityFieldFactory.Create(BenefitArchiveContactFieldIndex.Firstname),"len");
      fields[0].ExpressionToApply=exp;

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

This gives the following sql query:

SELECT len([dbo].[TBLbenefitArchiveContact].[firstname]) AS [charsCount] FROM [dbo].[TBLbenefitArchiveContact]  GROUP BY [dbo].[TBLbenefitArchiveContact].[firstname]

Do you have any suggestions?

The groupby clause emit routine indeed doesn't respect set expressions. Now, it's easy for me to enable that, though at the same time, it will cause problems if people have added the same field to selectlist and to groupby clause in current code: by using a new runtime it will suddenly produce different queries and likely different resultsets.

So I can't fix it as this point.

I see your point

Going to make an Stored Procedure for this instead.