I'm able to use the Dynamic Query API to build a SELECT statement with a GROUP BY.  But, is there a way to extend that query to add a HAVING clause?

For example, using Northwind, I would like to use the Dynamic Query API to generate a SQL query that looks like the following SELECT statement:

SELECT c.CustomerID
FROM Customers c
LEFT JOIN Orders o ON  c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(*) >= @iOrderCnt

I can easily accomplish everything but the HAVING clause by using the following Dynamic Query in VB.NET:

Dim c As New CustomersQuery("c")
Dim o As New OrdersQuery("o")
c.Select(c.CustomerID)
c.LeftJoin(o).On(o.CustomerID = c.CustomerID)
c.GroupBy(c.CustomerID)

Is there anyway to get the HAVING clause implemented using the Dynamic Query API?

http://www.entityspaces.net/blog/2008/03/03/EntitySpaces+2008+Dynamic+SubQuery+Showcase.aspx

Look here. I suspected you will find your answer here.

Well, I suppose in many instances one could "rewrite" this sort of query to use Sub Queries.  But, it seems to me that a basic HAVING clause should be supported natively in the Dynamic Query API (or via some sort of "workaround" that the API enables).  I still do not see a way to do the comparison in a WHERE clause referencing an aggregate count from the inner/sub query.

I still do not see a way to accomplish the SQL below using the Dynamic Query API:

SELECT c2.[CustomerID] AS 'CustomerID',
c2.[CompanyName] AS 'CompanyName',
c2.[ContactName] AS 'ContactName',
c2.[ContactTitle] AS 'ContactTitle',
c2.[Address] AS 'Address',
c2.[City] AS 'City',
c2.[Region] AS 'Region',
c2.[PostalCode] AS 'PostalCode',
c2.[Country] AS 'Country',
c2.[Phone] AS 'Phone'
FROM (SELECT c.[CustomerID], 
      COUNT(o.[OrderID]) AS 'OrdCnt' 
      FROM [Customers] c 
      INNER JOIN [Orders] o ON o.[CustomerID] = c.[CustomerID] 
      GROUP BY c.[CustomerID]
     ) sub
INNER JOIN Customers c2 ON sub.CustomerID = c2.CustomerID
WHERE sub.OrdCnt = <parm value>

I wonder if adding the "< arbitrary sql goes here >" functionality mentioned in the post below and on the Roadmap will be done in a way as to enable us to add a HAVING?

http://community.entityspaces.net/forums/thread/6899.aspx

Anyone know what the status is of the Group By and Having functionality? If it's not going to be a feature what would be a work around with ES?

That feature request is still on our enhancement tracking list, but there is no scheduled release date.

The current work-arounds are the SubQuery suggestion above, or a Custom Load.