I am trying to implement the query below using LLBLGen:

SELECT COUNT(componentid) FROM
(
        select ss.componentid
        from
        StructuredSystem ss
        inner join StructuredSystemProducts ssp1 on ss.componentid = ssp1.systemid
            and ssp1.componentid = 3395
        inner join StructuredSystemProducts ssp2 on ss.componentid = ssp2.systemid
            and ssp2.componentid = 3864
        inner join StructuredSystemProducts sspCount on ss.componentid = sspCount.systemid
        where ss.componentid <> 0
        group by ss.componentid
        having count(sspCount.componentid) = 2
) foo

I coded this by building up the inner query, then using DataAccessAdapter.GetDBCount().

The generated SQL looks fine, EXCEPT that the inner query (the SELECT against my base Entity table, "StructuredSystem") includes all fields, as if I were doing a FetchEntityCollection(). Since the GROUP BY only requires the one field (componentId), having all the other fields in the SELECT causes a SQL error.

I just need some way to exclude the unnecessary fields from the SELECT list, but I didn't see a way to use an ExcludeIncludeFieldsList. Any suggestions would be appreciated.

This is using SQL 2005, LLBLGen 2.5 (v2.0.50727), Adapter templates

Could you please post the code you have which doesn't work? Perhaps you made a small error...

OK, you asked for it

Int32 numProducts = sys.StructuredSystemProducts.Count;
IRelationPredicateBucket bucket = new RelationPredicateBucket();
//add the 'count' join
bucket.Relations.Add(StructuredSystemEntity.Relations.StructuredSystemProductsEntityUsingSystemId, "sspCount", JoinHint.Inner);

//add the joins & WHERE for each Product
Int32 counter = 1;
foreach (StructuredSystemProductsEntity ssp in sys.StructuredSystemProducts)
{
    string alias = String.Format("ssp{0}", counter);
    bucket.Relations.Add(StructuredSystemEntity.Relations.StructuredSystemProductsEntityUsingSystemId, alias, JoinHint.Inner);
    IPredicateExpression joinFilter = new PredicateExpression(StructuredSystemProductsFields.ComponentId.SetObjectAlias(alias) == ssp.ComponentId);
    bucket.PredicateExpression.AddWithAnd(joinFilter);
    counter++;
}

//add the HAVING COUNT filter
IGroupByCollection groupBy = new GroupByCollection(StructuredSystemFields.ComponentId);

groupBy.HavingClause = new PredicateExpression(StructuredSystemProductsFields.ComponentId.SetObjectAlias("sspCount").SetAggregateFunction(AggregateFunction.Count) == numProducts);


//add the WHERE to filter our this particular system from the
//check (if a New system, ID is 0 and nothing will match that, so 
//it's still safe)
bucket.PredicateExpression.AddWithAnd(StructuredSystemFields.ComponentId != sys.ComponentId);

Int32 numDupes = 0;
using (DataAccessAdapter adp = new DataAccessAdapter())
{
    
    numDupes = adp.GetDbCount(new EntityCollection<StructuredSystemEntity>(), bucket, groupBy);

}

Thanks.

I think this is by-design, as fields in GroupBy clause should be the same as in you SELECT clause. You, however could use these workarounds:

Workaround 1

...
EntityFields2 fields = new EntityFields2(1);
fields.DefineField((IEntityField2) groupBy[0], 0);
...
using (DataAccessAdapter adp = new DataAccessAdapter())
{   
    numDupes = adp.GetDbCount(fields, bucket, groupBy);
}

Workaround 2

...
using (DataAccessAdapter adp = new DataAccessAdapter())
{
    numDupes = (int)adp.GetScalar((IEntityField2)grouper[0], null, AggregateFunction.Count, 
                         bucket.PredicateExpression, grouper, bucket.Relations);
}

Hope helpful.

Excellent! My problem was that I was not familiar with using the overloads that take IEntityFields(2)- those workarounds did precisely what I wanted, only including the single desired field in the SELECT. This will help me with a few other things as well.

Thanks again for the assistance.