Designer TypedList Count

Posts   
 
    
tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 02-Aug-2007 19:31:32   

Hello,

This is not a show stopper and may be by design... I created a TypedList, had a Aggregate Count applied to a boolean column, and when the designer generates the code and you try to pull the count from the Fetched TypedList, it is invalid because the type it generates is a boolean (the original column that was counted on)...

It seems to me that if you apply a count to any field, whether a sql bit, varchar, etc... if you apply a COUNT function, it should change the .NET Type to a int32...

Does that make sense?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 03-Aug-2007 08:55:28   

It seems to me that if you apply a count to any field, whether a sql bit, varchar, etc... if you apply a COUNT function, it should change the .NET Type to a int32...

Does that make sense?

Please you examine the generated SQL query to see what goes wrong. Did you specify a "Group By" clause to the fetch method? I think the Aggregations won't get emitted unless you do Grouping.

tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 03-Aug-2007 08:58:30   

Walaa wrote:

It seems to me that if you apply a count to any field, whether a sql bit, varchar, etc... if you apply a COUNT function, it should change the .NET Type to a int32...

Does that make sense?

Please you examine the generated SQL query to see what goes wrong. Did you specify a "Group By" clause to the fetch method? I think the Aggregations won't get emitted unless you do Grouping.

I did not even run the code, the reason I found this, the property on the TypedRow was a boolean... Such as:

MyTypeRow.MyCountedColumn <--- was a boolean - not an int like you might think for a counted field.

So is there something more I should have done? You cannot apply a sort when building a typed list... so not sure how to do that in the designer...?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 03-Aug-2007 09:04:13   

So is there something more I should have done? You cannot apply a sort when building a typed list... so not sure how to do that in the designer...?

Just try to run the code and fetch the TypedList as you would normally do, pass in a GroupByCollection, and check the returned resultset and the generated query.

tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 03-Aug-2007 09:21:15   

Walaa wrote:

So is there something more I should have done? You cannot apply a sort when building a typed list... so not sure how to do that in the designer...?

Just try to run the code and fetch the TypedList as you would normally do, pass in a GroupByCollection, and check the returned resultset and the generated query.

I already changed the TypedList to Count on the ID, which is an int... so yes, the code does work fine...

What I was really talking about is that the generated code creates a few items such as:

GetGiveAwayCountTypedList GetGiveAwayCountTypedListFieldIndex GetGiveAwayCountRow

The GetGiveAwayCountTypedList is a collection of GetGiveAwayCountRow objects... and if I cast the item to that specific object, GetGiveAwayCountRow ... that is the object that has the incorrect boolean... It is based off the actual type of the column in the database and does not take into account that the Aggregate of Count should return an int or something more than a boolean...

For example if you counted on a varchar, then it would be based on a string... It really does not matter what field you count on, the predicate to get the result if the real counter...

What I don't understand is if LLBLGen generates the three types above (the TypedList, ListFieldIndex and Row) ... it seems the Row version should return the correct properties on the defined typed.

If this does not make sense, I will let this go and just consider this something of me using LLBLGen wrong... not trying to say it does not generate the right result, just the generate Row type has the mapped column as a count as an incorrect type ... Not sure how to better explain it. Sorry if this has been too confusing.

Edit: Here is some code:

GetGiveAwayCountTypedList list = new GetGiveAwayCountTypedList();
         IPredicateExpression filter = new PredicateExpression( MemberProgramsFields.MemberId == memberId );
         filter.Add( MemberProgramsFields.ProgramId == programId );
         adapter.FetchTypedList( list, filter );
         foreach( GetGiveAwayCountRow row in list.Rows )
         {
            ... do some work...
            int count = row.GiveAwayCount; <--- the GiveAwayCount was a boolean
            ... do some more work...
         }

This code is not all inclusive in the example, what I really wanted to show is the foreach cast the rows to the GetGiveAwayCountRow that LLBLGen generated... and the only mapped column is the GiveAwayCount (that is an alias) the was counted on a boolean column...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 03-Aug-2007 16:51:12   

The problem is that the result of the aggregate is undefined: it's unknown what the .NET type will be, as different databases will return different .net types. So we leave the type as is.

Frans Bouma | Lead developer LLBLGen Pro
tzarger
User
Posts: 87
Joined: 14-Jun-2007
# Posted on: 03-Aug-2007 17:44:01   

Otis wrote:

The problem is that the result of the aggregate is undefined: it's unknown what the .NET type will be, as different databases will return different .net types. So we leave the type as is.

Thanks Frans... so it is by design... to use the aggregate of of count for example, access the column via the generic datarow rather than the typed Row...?

int count = int.Parse( list.Rows[0]["GiveAwayCount"].ToString() )

Or how do you use Counts, etc from a TypedList?

Another note, do you think it would be useful if you allow the end-user select the .NET type in the designer when they are working with TypedLists? Since that would be an explicit call made by the user, it would make sense as they should know the type generated by their DB ... or is there more to the typing from the DB than meets the eye...?

( Should I put that in as a feature request that we might be able to get into v2.5? )

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Aug-2007 14:14:16   

tzarger wrote:

Otis wrote:

The problem is that the result of the aggregate is undefined: it's unknown what the .NET type will be, as different databases will return different .net types. So we leave the type as is.

Thanks Frans... so it is by design... to use the aggregate of of count for example, access the column via the generic datarow rather than the typed Row...?

int count = int.Parse( list.Rows[0]["GiveAwayCount"].ToString() )

Or how do you use Counts, etc from a TypedList?

That's in general what you should do indeed. It's a little painful, I admit. The reason we made editing aggregates in the designer of the typedlist editor was that it was less cumbersome to set up the aggregates per field. Though picking 'object' as type is also not the right move IMHO.

Another note, do you think it would be useful if you allow the end-user select the .NET type in the designer when they are working with TypedLists? Since that would be an explicit call made by the user, it would make sense as they should know the type generated by their DB ... or is there more to the typing from the DB than meets the eye...?

( Should I put that in as a feature request that we might be able to get into v2.5? )

V2.5 is feature complete, no changes are made other than bugfixes.

One could use typeconverters, but the designer indeed has to know the aggregate result type to be able to allow the right typeconverter.

Allowing the specification of the type of the field always needs some sort of conversion as it's not said that the type specified by the user is also the type of the value. simple_smile

In v3 we'll likely go a different route with specifying the elements in the project so we'll take this into account. (I'll file a feature request simple_smile ).

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 06-Aug-2007 07:50:12   

I agree with tzarger - if you're doing a Count aggregate on a bool field then regardless of database type, the last thing you want is a bool property. smile

What would be useful (and in entities too maybe?) is to be able to specify a complete custom placeholder field. Developer gets to specify the .NET type and it is up to him/her to ensure that it is correct for aggregate or, if a ScalarExpressionQuery is used, it matches the output for that.

Is that what you are planning for v3?

Cheers Simon

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-Aug-2007 13:06:38   

simmotech wrote:

I agree with tzarger - if you're doing a Count aggregate on a bool field then regardless of database type, the last thing you want is a bool property. smile

Heh, ok I can't babble my way out of that one wink

What would be useful (and in entities too maybe?) is to be able to specify a complete custom placeholder field. Developer gets to specify the .NET type and it is up to him/her to ensure that it is correct for aggregate or, if a ScalarExpressionQuery is used, it matches the output for that.

Is that what you are planning for v3?

For v3 we're planning a (text based) DSL based approach, with a new model (based on current one) and different ways of defining changes for the model so undo-redo is possible for example. This also asks for a different set of designers, and the current set of designers are pretty much EOL after v2.5, as in: to get to the next level, a new approach is needed.

The thing is: defining a placeholder field is of course great, and you then would want to define an expression there, to be used with the field. So that's pretty complex to do in a designer like we have today.

Frans Bouma | Lead developer LLBLGen Pro