Hey all, i'm trying to achieve the following query with LINQ (or using predicates for that matter but i figured linq would be easier)

SELECT CategoryID, Description, (SELECT COUNT(*) FROM Projects WHERE Project.ProjectID IN (SELECT ProjectID FROM ProjectGroup WHERE ProjectGroup.ProjectID IN (SELECT ProjectID FROM ProjectGroupCategories WHERE ProjectGroupCategories.CategoryID = Category.CategoryID) ) ) AS ProjectCount FROM Category

I have the following tables

ProjectGroup Projects ProjectGroupCategories Category

I want to get a list of categories and the number of Projects associated with the ProjectGroup which is associated to the category through the m-n lookup ProjectGroupCategories.

It would be nice to also see how to exclude all categories which do not have any projects.

Any help is greatly appreciated. Thanks.

First, using LLBLGen expressions and predicate system:

SELECT CategoryID, Description,

You should use DynamicList.

(SELECT COUNT(*) FROM Projects ... AS ProjectCount

Here you should use ScalarQueryExpression (go down until find the title).

WHERE Project.ProjectID IN
    (SELECT ProjectID FROM ProjectGroup WHERE ProjectGroup.ProjectID IN
        (SELECT ProjectID FROM ProjectGroupCategories WHERE ProjectGroupCategories.CategoryID = Category.CategoryID)
    )
) 

and this is a FieldCompareSetPredicate (nested).

For LINQ2LLBL, the concepts are the same, you only need to be confident with LINQ systaxis. I recommend you to read the LINQ2LLBL's section manual and see the examples at [LLBLGen Pro v2.6 Installation Folder]\Sourcecode\LinqUnitTests.