I'm trying to write the equivalent of this SQL statement:

SELECT * FROM [Brick]
INNER JOIN [BrickTagMapping] ON [Brick].[BrickId]=[BrickTagMapping].[BrickId] INNER JOIN [Tag] ON [Tag].[TagId]=[BrickTagMapping].[TagId]) WHERE [Tag].[Name] IN ('ParamA','ParamB') GROUP BY [Brick].[BrickId] HAVING COUNT( [Brick].[BrickId] )=2 /* count of number of items in IN list */

So far, I've got

//IGroupByCollection groupByClause = new GroupByCollection(); //groupByClause.Add(BrickFields.BrickId);

EntityCollection<BrickEntity> bricks = new EntityCollection<BrickEntity>(); IRelationPredicateBucket expr = new RelationPredicateBucket(); expr.Relations.Add(BrickEntity.Relations.BrickTagMappingEntityUsingBrickId); expr.Relations.Add(BrickTagMappingEntity.Relations.TagEntityUsingTagId); expr.PredicateExpression.Add(TagFields.Name == tags); da.FetchEntityCollection(bricks, expr);

There's two problems I'm having though.

1) I can't specify the groupByClause (or add the Having clause) - because FetchEntityCollection doesn't seem to allow me to do this? How can I get around this, or can anyone suggest a way to write an equivalent query?

2) The SQL at the moment that LLBLGen is spitting out includes SELECT DISTINCT rather than just SELECT

Any suggestions? Thanks everyone!

you need to use a typed/dynamic list to use group by/having. this is by design. see the help docs for more info Concepts > Entities, typed lists and typed view

Sure, you can set the Having clause on the groupByClause like

groupByClause.HavingClause = new PredicateExpression(TransactionDetailItemFields.Quantity.SetAggregateFunction(AggregateFunction.Sum) < 0)

... so is there any way to get a EntityCollection back, using TypedLists? I hate the idea of having to return a different typed object just because I'm using a particular SQL syntax in the query... when it's still returning what is just a collection of entities...?

Sure, "convert" the EntityCollection to a typedlist by fetching the entityfields of the collection -> i.e.

Dim collfields As IEntityFields2 = CType(coll.EntityFactoryToUse.CreateFields(), EntityFields2)

...where coll is a entitycollection. You can use this collfields on a adapter.fetchtypedlist(......)

It's not that direction I'm having trouble with - it's getting an EntityCollection back out again....? Thanks for your help everyone!

You may use Projection, to project the resultSet to an Entity Collection. Please check the LLBLGen Pro manual "Using the generated code -> SelfServicing/Adapter -> Fetching DataReaders and projections"

By the way: Would that mentioned query actually work?

SELECT * FROM [Brick] 
INNER JOIN [BrickTagMapping] ON [Brick].[BrickId]=[BrickTagMapping].[BrickId] 
INNER JOIN [Tag] ON [Tag].[TagId]=[BrickTagMapping].[TagId]) 
WHERE [Tag].[Name] IN ('ParamA','ParamB')
GROUP BY [Brick].[BrickId]
HAVING COUNT( [Brick].[BrickId] )=2

Can you have a Group By, while you have fields in the select list without aggregations?