Is there a way to apply a GroupBy clause for a fetch into an entity collection? I am currently experimenting with Projections, but was wondering if there was another way.

Thanks for any help

There is no a way to do a direct groupBy in a EntityCollection fetch action. That doesn't make sense or that would permit senseless calls.

What you can do instead of using projections is use a _FieldCompareSetPredicate _in your predicate. The SQL for that should look like:

...
WHERE
CustomerId IN 
   (SELECT CustomerId, SUM(Order.Freight) 
     ...
    GROUP BY CustomerId
   )

See LLBLGenPro Help - Using generated code - (Adapter | SS) - Filtering and sorting - The predicate system - FieldCompareSetPredicate.

Hope helpful

Hi daelmo - thanks for your reply

There is no a way to do a direct groupBy in a EntityCollection fetch action. That doesn't make sense or that would permit senseless calls.

Could you elaborate a little further on why this would not make sense? I can provide examples of where I think it would be useful to have a groupby clause against a single table\entity query - if this would be helpful. One example would be where an entity is built against a view.

Thanks for your example of using the FieldCompareSetPredicate. As far as i can see from the documentation this predicate it lets you generate the following type of SQL:

Field IN (SELECT Field FROM Foo WHERE ...) 

However, in your example above you show generated SQL which has a GROUP BY clause in it (which is what i am after). Could you elaborate on how you achieved this?

Thanks

Are you trying to use a group by clause when fetching the collection or are you trying to found a group-by-like functionality against the collection already fetched. The later can't be accomplished, bet for the first one look for IGroupByClause. Theres also an example in the HowDoI section of the help regarding Group by clauses.

Hi Goose

Thanks for your reply... I believe you can actually do a groupby against an already fetched (in-memory) collection using the AggregateSetPredicate predicate - however this is not what i am trying to do.

I would like to fetch an entity collection, and apply a groupby clause. My problem is that there seems to be no overload of the DataAccessAdapter "[b]FetchEntityCollection[/b]" method that accepts a groupBy clause. I am trying to get the the bottom of if there is a way to do this that i don't know about OR find out if there is a good reason why there is not.

Maybe some real world examples will help illustrate what i am trying to do

Example 1

Table/Entity pk_fk_productID (int) pk_fk_relatedProductID (int)

Task To retrieve a collection of unique relatedProductIDs where productID is in (1, 2, 3 ,4, 5)

Comments I think this would be a fairly straight forward collection fetch if you could groupby pk_fk_relatedProductID. Without a groupby you can get duplicate related productIDs as the select is against several productIDs

Example 2

View/Entity ProductsInCategory (a product can be in multiple categories, so this view can have duplicate products in)

Task Fetch a collection based on certain given user search parameters, but don't return duplicate products

Comments Again I think this would be a fairly straight forward collection fetch if you could groupby all the product fields. Without it, you any products that are in more than one category will be returned in duplicate.

There are alternatives that can be use (such as Projection), but this seems a complex solution, when a groupby clause on an entity collection would seem to be a simple alternative.

Any input appreciated.

jtoast wrote:

Hi daelmo - thanks for your reply

There is no a way to do a direct groupBy in a EntityCollection fetch action. That doesn't make sense or that would permit senseless calls.

Could you elaborate a little further on why this would not make sense? I can provide examples of where I think it would be useful to have a groupby clause against a single table\entity query - if this would be helpful. One example would be where an entity is built against a view.

Thanks for your example of using the FieldCompareSetPredicate. As far as i can see from the documentation this predicate it lets you generate the following type of SQL:

Field IN (SELECT Field FROM Foo WHERE ...) 

However, in your example above you show generated SQL which has a GROUP BY clause in it (which is what i am after). Could you elaborate on how you achieved this?

Thanks

A _FieldCompareSetPredicate _constructor overload lets you create a predicate with a groupBy clause, so my example above is possible. If you write your wanted SQL we can assist you in how to use such predicate.

This is copied from LLBLGenPro reference manual:

**C# ** public FieldCompareSetPredicate( IEntityField field, IEntityField setField, SetOperator operatorToUse, IPredicate filter, IRelationCollection relations, string objectAlias, long maxNumberOfItemsToReturn, ISortExpression sorter, bool negate, IGroupByCollection groupByClause )

Parameters

_field _ field to compare to the set results. specify null if EXISTS operator is used

_setField _ field to base the set on

_operatorToUse _ operator to use as operator between field and the set

_filter _ filter to use in the set query. Can be null

_relations _ relations to use in the setquery. Can be null

_objectAlias _ Alias for the object the field belongs to. Used to identify which entity to use when the entity is present multiple times in a relation collection. Alias has to match an alias specified in the relation collection of the query this predicate is part of or should be left empty if no alias is specified (or no relation collection is used in the query this predicate is part of). In that case, use another overload.

_maxNumberOfItemsToReturn _ the maximum amount of rows to return in the set query.

_sorter _ The sort expression to use in the set query

_negate _ negate the compare expression

**_groupByClause _** The Group By clause to use in the set query.

Hope helpful

Hi Daelmo

Thanks for that info. It looks like would work great for example 1... do you have any suggestions for how a groupby filter could be applied for example 2 (above)?

Thanks

jtoast wrote:

Example 2

View/Entity ProductsInCategory (a product can be in multiple categories, so this view can have duplicate products in)

Task Fetch a collection based on certain given user search parameters, but don't return duplicate products

Comments Again I think this would be a fairly straight forward collection fetch if you could groupby all the product fields. Without it, you any products that are in more than one category will be returned in duplicate.

There are alternatives that can be use (such as Projection), but this seems a complex solution, when a groupby clause on an entity collection would seem to be a simple alternative.

If you want two-way interaction (Fetch and Save) I would go on fetch entity collection (not view).

If that is the case, you could Retrieve the Product Collection with a prefetch path _ProductEntity.PrefetchPathCategoryCollectionViaProductsInCategory. _The Product collection and the Category collection are fetched and related. You, at code could do something like:

string firstCategoryName = productCollection[n].CategoryCollectionViaProductsInCategory[xx].CategoryName;

jtoast wrote:

Fetch a collection based on certain given user search parameters, but don't return duplicate products

If you retrieve uniques products from ProductsInCategory, how do you determine what row to retrieve, for example, if you have the following data at ProductsInCategory table,

PId ----- CId
1           50
1           60
1           70
2           50
2           60
3           70

after a given user filter, what results do you expect?

Hey Daelmo

This is a question i have had before too...

If you retrieve uniques products from ProductsInCategory, how do you determine what row to retrieve, for example, if you have the following data at ProductsInCategory table,

Code: PId ----- CId 1 50 1 60 1 70 2 50 2 60 3 70

after a given user filter, what results do you expect?

So in this example may want to "groupby PId" so the following can be returned:

PId 1 2 3

I know that it is not easily/possible to add a groupby to an entity collection fetch... i would like to know what the reason for this is. Here is what i am confused over:

A groupby clause can be added to a typedview fetch. A groupby clause can not be added to a collection fetch. This doesn't make sense to me because both the typed view and the entitycollection could be based on the same view (as entity's can be build on views). If an EntityCollection and a TypedView can use the same data, surely there is the potential need to filter them in the same ways (including groupby).

The answer may be that this is a feature that has not been added yet. Alternatively, the answer may be that there is some technical or conceptual reason that makes it impossible/a bad idea to use a groupby filter when retrieving an entity collection...

So now i guess my question is:

Why can you not add a groupby clause to an entity collection fetch?

Thanks for sticking this one out

Why can you not add a groupby clause to an entity collection fetch?

Simply becaue an entity represents a row in the table (speaking about data). So if you perform a group by on a collection, you will have to aggregate some fields, which won't give a correct representation of the data in the database.

or you will have to only fetch those grouped by fields as in you example here:

So in this example may want to "groupby PId" so the following can be returned: PId 1 2 3

Which shouldn't be represented in an EntityCollection, because you are fetching a subset of the fields, and you can't re-fetch the rest of the fields later on.

That's why if you want to fetch a grouped resultSet you should use a DynamicList or a TypedList.

Think of it as: Fetching an Entity or an EntityCollection is doing a SELECT * Any select list other than the SELECT * means that should use another data container.