I'm working on paging in my middle tier and I'm trying to call GetDbCount to get a count of all the records that exist for a given filter. I'm also building a sort expression and an includefields collection that I pass to GetMulti.

For some of my views, when I call the GetDbCount, I get an oracle error regarding one of my fields in the select clause that you're building (that I don't seem to have any control of). Invalid identifier. When I do the actual select, it works, and that select contains the very same field that the count query failed on.

Why is the GetDbCount building such a huge select? All I need is a count of rows. Is there any way to limit that? I don't see an overload for passing an includeFields to GetDbCount.

Could you please post some relevant information? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

• The exact exception message and stack trace of the exception.
• The LLBLGen version and runtime library version.
• The "generated sql" of the problematic fetch or dbcount.

LL 2.0, Oracle, Self-Servicing.

Never mind on the oracle error. DbAdmin replied about a new column on table. Although the select from the view wouldn't generate an error, only the count, interesting....

Anyways, only remaining issue is is there a way to restrict the select clause that is generated when using GetDbCount? Ideally, I'd think it would only add the primary keys or just whatever the first column is. Seems like a waste to select all columns.

thanks

The duration of the two queries are almost equal. However if you don't want that, you could perform a myCollection(someFieldIndex, AggregateFunction.CountRow);.

daelmo wrote:

The duration of the two queries are almost equal. However if you don't want that, you could perform a myCollection(someFieldIndex, AggregateFunction.CountRow);.

We discovered that the performance is significately different for view based entities due to calculated fields, etc.

So I tried your suggestion. I'm calling GetScalar. But it looks like it's still referencing all fields. I'm calling the GetScaler that takes FieldIndex,IExpression,AggregateFunction,and Predicate.

I'm setting FieldIndex to the first field of the primary key,IExpresssion is null, AggregrateFunction is CountRow and predicate is my filter. It's returning the right count but I'm worried it's just doing the same thing as before. I would have expected Count(NameOfFieldIndexThatISupplied) as RowCount or something like that.

It's generating a query that includes

SELECT COUNT(*) AS "NameOfFieldIndexThatISupplied" FROM ....

Am I doing something wrong or is this the intended behaviour? If intended, then how can I accomplish what I want?

Please submit the following: 1- The exact code snippet you are using. 2- The exact generated SQL. 3- The LLBLGen Runtime library version number (please check the link posted by daelmo).

Code is part of a framework that is using reflection to execute:

string fieldIndexTypeName = this.BusinessObjectCollectionType.FullName.Replace("CollectionClasses.", "").Replace("Collection", "FieldIndex"); Type fieldIndexType = this.BusinessObjectType.Assembly.GetType(fieldIndexTypeName);

                    Type[] types = new Type[] { fieldIndexType, typeof(IExpression), typeof(AggregateFunction), typeof(IPredicate) };
                    MethodInfo getScalarMethod = this.BusinessObjectCollectionType.GetMethod("GetScalar", types);

                    List<string> pKeys = GetDataKeyNames();
                    System.Reflection.FieldInfo fieldPropInfo = fieldIndexType.GetField(pKeys[0]);
                    object field = fieldPropInfo.GetValue(null);

                    object[] parameters = new object[4];
                    parameters[0] = field;
                    parameters[1] = null;
                    parameters[2] = AggregateFunction.CountRow;
                    parameters[3] = searchConfig.Filter;

decimal rowcount = (decimal)getScalarMethod.Invoke(collection, parameters);

Count query with no search criteria entered:

Query: SELECT * FROM (SELECT COUNT(*) AS "UserId" FROM "DATA"."USER_PROFILE") WHERE rownum <= 1

Count query with search criteria:

Query: SELECT * FROM (SELECT COUNT(*) AS "UserId" FROM "DATA"."USER_PROFILE" WHERE ( ( ( "DATA"."USER_PROFILE"."USER_ID" LIKE :UserId1)))) WHERE rownum <= 1 Parameter: :UserId1 : String. Length: 4. Precision: 0. Scale: 0. Direction: Input. Value: "%AD%".

Version Number: 2.6.08.1125

Please try AggregateFunction.Count, instead of AggregateFunction.CountRow

AggregateFunction.CountRow ----> Count(*) AggregateFunction.Count ----> Count(field)

Walaa wrote:

Please try AggregateFunction.Count

Thanks! That worked.