Is it possible to get the ROW NUMBER of a query?

I see LLBL generates a paginated query like below. I like to include the "__rowcnt" field to my Entity object. I have no idea if there a way to achieve it.

WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT DISTINCT TOP 20 ... FROM ... WHERE ... ORDER BY ... ASC) AS _tmpSet)

  SELECT ...
  FROM __actualSet 
  WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd 
  ORDER BY [__rowcnt] ASC

Why do you want to include __rowcnt to your entity? AFAIK, this value have no significance outside the context of the query.

Thanks Walaa for responding. One reason is I need to display the record number on the UI.

Here is how you can do it from an old Helpdesk thread:

You should implement IExpression as in the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3829

Then you should use a DynamicList to fetch the needed data. And use the SetExpression() method to set an instance of the IExpression class you have implemented to one of the ResultSet fields.

The following is the Expression implementation.

[Serializable] public class RowNumberExpression : Expression { public RowNumberExpression(IEntityField2 field): base(field) { }

    public override string ToQueryText(ref int uniqueMarker, bool inHavingClause)
    {
        return string.Format("ROW_NUMBER() OVER (order by {0})", base.ToQueryText(ref uniqueMarker, inHavingClause));
    }
}