SELECT COUNT(

Posts   
 
    
swallace
User
Posts: 648
Joined: 18-Aug-2003
# Posted on: 23-Jan-2004 17:12:26   

I think I've read the answer before, but I can't find it in the new search.

Is there a way to execute a SELECT COUNT(0) command against a Collection without populating with GetMulti and then using the .Count? Sure would save alot of hits.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Jan-2004 17:20:44   

Not at the moment...

You can create the query yourself of course, using a connection created with the DbUtils.CreateConnection() method, however it's not optimal...

Frans Bouma | Lead developer LLBLGen Pro
mdissel
User
Posts: 92
Joined: 16-Sep-2003
# Posted on: 23-Jan-2004 19:38:52   

we could add this funcionality to the EntityCollection class.. If you request the .Count property and the collection is not loaded yet, then use a Select Count(*) query and store the Count for further reference. When you access an entity in the ItemCollection just check if the only the count is loaded else load the complete collection (or the current entity, extra option)).

ps just my 2 cents, maybe i'm wrong, i don't have that much experience with LLBLGen yet.

Regards,

Marco

swallace
User
Posts: 648
Joined: 18-Aug-2003
# Posted on: 23-Jan-2004 20:51:16   

I like that idea, or even a GetCount(). I would recommend a SELECT COUNT(0) rather than SELECT COUNT() for speed, though I've no idea how that translates between T-SQL and that Oracle crp.

Also, I'm sure there's much more involved, like the predicates and other relations. Perhaps a

.GetCount( PredicateExpression mPredicate )

that picks up all the proper relationships, similar to the GetMulti[childtable] functions.

I've got to produce a list of categories, with the names appended with the count within that category. Monster SQL hits without a good count function, and I'm too hooked on LLBLGen (ahem, lazy...) to do it myself. In addition the items within the categories have prediates beyond just being a child of that category (predicates involved).

I can see feature creep later where people will want more than COUNT(0), but just that much with the predicates and relationships would be ideal today.

simple_smile

Christian
User
Posts: 8
Joined: 13-May-2004
# Posted on: 13-May-2004 15:07:08   

.GetCount( PredicateExpression mPredicate )

Something like .GetCount would be great. smile

I have to do a lot of workaround-ing to retrieve the total row count according to a PredicateExpression. There is no fun in stuffing a complex PredicateExpression into a stored procedure disappointed

Christian

Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 04-Aug-2004 01:05:47   

Otis wrote:

Not at the moment...

You can create the query yourself of course, using a connection created with the DbUtils.CreateConnection() method, however it's not optimal...

Frans, has the COUNT(0) feature been addressed yet?

Marcus

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Aug-2004 10:50:29   

2 days ago I've completed GetDbCount() and GetScalar() functionality in the new runtime libs in development simple_smile (complete with filter support, aggregate on field or row, expressions etc. simple_smile )

Frans Bouma | Lead developer LLBLGen Pro
Marcus avatar
Marcus
User
Posts: 747
Joined: 23-Apr-2004
# Posted on: 04-Aug-2004 11:09:55   

Otis wrote:

2 days ago I've completed GetDbCount() and GetScalar() functionality in the new runtime libs in development simple_smile (complete with filter support, aggregate on field or row, expressions etc. simple_smile )

smile

Posts: 11
Joined: 09-Jul-2004
# Posted on: 06-Aug-2004 10:59:37   

when will the runtime be available?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Aug-2004 11:12:46   

Gareth wrote:

when will the runtime be available?

When I think the bugs are gone. Beta will hopefully start next week (development takes a little longer than anticipated) and then it depends on how testing goes. We had some problems with beta projects in the past (too early stuff got released) so this beta will take as long as it takes.

Frans Bouma | Lead developer LLBLGen Pro
JohnRLewis avatar
JohnRLewis
User
Posts: 27
Joined: 30-Aug-2004
# Posted on: 02-Sep-2004 16:08:09   

Otis wrote:

2 days ago I've completed GetDbCount() and GetScalar() functionality in the new runtime libs in development simple_smile (complete with filter support, aggregate on field or row, expressions etc. simple_smile )

I am using the adapter scenario, and am not able to find where these new functions are located. I am using the latest beta libraries.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Sep-2004 16:27:32   

JohnRLewis wrote:

Otis wrote:

2 days ago I've completed GetDbCount() and GetScalar() functionality in the new runtime libs in development simple_smile (complete with filter support, aggregate on field or row, expressions etc. simple_smile )

I am using the adapter scenario, and am not able to find where these new functions are located. I am using the latest beta libraries.

True, these are not defined in adapter, you need to produce a little bit of logic and call GetScalar. The following example gets the amount of orders in the database.

int amountOrderRows = (int)adapter.GetScalar(EntityFieldFactory.Create(OrderFieldIndex.OrderId), AggregateFunction.CountRow);

The reason for not having these GetDbCount routines is simple: there is no typed collection per entity so there is no location to place these routines, that's why the somewhat different approach.

Frans Bouma | Lead developer LLBLGen Pro
rich
User
Posts: 22
Joined: 09-Sep-2004
# Posted on: 24-Sep-2004 15:45:45   

Otis, GetScalar() it great but seems to be missing from the documentation. The code snippet above fails on the cast if the database table contains no rows. I guess it is returning null??

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 24-Sep-2004 16:15:33   

It is in the latest docs in the latest beta (expressions and aggregates and reference manual). I return the value returned by the ExecuteScalar() method, so this can indeed be null, it is an object after all. The problem is, I can't return 0 if it is null, as you can't cast 0 (int32) to a float for example...

Frans Bouma | Lead developer LLBLGen Pro
rich
User
Posts: 22
Joined: 09-Sep-2004
# Posted on: 24-Sep-2004 16:25:43   

...and indeed the result of the operation may be zero and you would want to know that..!

Fabrice
User
Posts: 180
Joined: 25-May-2004
# Posted on: 01-Oct-2004 14:05:44   

I think I missed something Why not simply create this function (on the adapter) :

public int GetDbCount(IEntityField2 field) 
            {
                object result = this.GetScalar(field, AggregateFunction.CountRow);
                if (result is int)
                    return (int)result;
                return 0;
            }

An then :

int amountOrderRows = adapter.GetDbCount(EntityFieldFactory.Create(OrderFieldIndex.OrderId));
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Oct-2004 14:37:45   

The philosophy is: you get the DB count of a given collection of entities. Normally you would have the collection class and call GetDBCount, or the typed list class and call GetDBCount on that. With adapter that's not possible. So it came down to a different idea behind GetDbCount, and I didn't like that much, so I dropped the GetDbCount for Adapter because it would be almost the same as the GetScalar method, as you have to create the field anyway.

But you're free to add the method in an include template and generate it right into the DataAccessAdapter simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Fabrice
User
Posts: 180
Joined: 25-May-2004
# Posted on: 01-Oct-2004 14:40:39   

ok simple_smile