The other day, Lane (sits in same little office as me, sometimes works together with me) asked how to find all the clients with at least one active plan:
He had written some code that did the filtering in code (i.e., get got a list of all plans, then accumulated a distinct list of clients for the plans that were active.) This code didn't work for a completely unrelated reason. We fixed that problem and left it at that. This was a good problem for me to play with to learn more about projections and subqueries in NHibernate, so I took the problem home with me. This stuff confuses me and seems to be rather poorly documented: there is a section of the NH documentation that touches on this functionality but doesn't really explain what a projection is, or any other background info.
So, my menal definition of a projection is, you build an NH query then get something else about the set of objects your query defines, such as aggregate information.
Of course, there is also this post from Oren, which has lots of clues and a good description of his sample (I find the NH documentation samples kind of hard to follow sometimes,) but his solution only works on the "trunk" (circa Nov. 2006). I can't find where the "Expression.Property" is implemented in 1.2GA NH.
So for the clients-with-active-plans problem, My first thought was, "Let's just group the active plans by client, and project the clients":
IList<Client> clientsWithActivePlans =
session.CreateCriteria(typeof(Plan))
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("Client"))
.Add(Projections.GroupProperty("Client")))
.Add(Expression.Eq("IsActive", true))
.List<Client>();
This results in:
I am expecting a list of 3 clients: I have 5 clients in the database, one with no plans, one with plans but none active, the rest with active plans.
"from" does have 3 entries, but why does each row have 2 objects in it? I was expecting just the Client object. Here is the SQL:
SELECT
this_.ClientID as y0_,
this_.ClientID as y1_
FROM
[Plan] this_
WHERE
this_.IsActive = @p0
GROUP BY
this_.ClientID;
@p0 = 'True'
Not sure why ClientID is selected twice...what if I remove the Property projection...does group by get me the grouping column/object automatically?
IList<Client> clientsWithActivePlans =
session.CreateCriteria(typeof(Plan))
.SetProjection(Projections.GroupProperty("Client"))
.Add(Expression.Eq("IsActive", true))
.List<Client>();
Yields this SQL:
SELECT
this_.ClientID as y0_
FROM
[Plan] this_
WHERE
this_.IsActive = @p0
GROUP BY
this_.ClientID; @p0 = 'True'
This looks better...but it only fetches the IDs. The Clients are then lazy loaded if you iterate over them:
Console.WriteLine("Clients with active Plans:");
foreach (Client client in clientsWithActivePlans)
Console.WriteLine(client.Name);
Generates:
SELECT
client0_.ID as ID0_0_,
client0_.Name as Name0_0_
FROM
Client client0_
WHERE
client0_.ID=@p0;
@p0 = '2'
For each Client in the list.
How about making this query into a detached criteria, and fetching full Clients where the Client exists in the above query (i.e., use the above query as a subquery.)
DetachedCriteria clientIDsWithActivePlans =
DetachedCriteria.For<Plan>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("Client")))
.Add(Expression.Eq("IsActive", true));
IList<Client> clientsWithActivePlans = session.CreateCriteria(typeof(Client))
.Add(Subqueries.PropertyIn("ID", clientIDsWithActivePlans))
.List<Client>();
Gives me one query to the database:
SELECT
this_.ID as ID0_0_,
this_.Name as Name0_0_
FROM
Client this_
WHERE
this_.ID in (
SELECT this_0_.ClientID as y0_
FROM [Plan] this_0_
WHERE this_0_.IsActive = @p0
GROUP BY this_0_.ClientID);
@p0 = 'True'
and no lazy loading the clients as we iterate over the list.
I think I've only just scratched the surface with what you could do with Projections and Subquery criterions in NHibernate. I hope Oren posts more about this and I hope I have more time to play around with this on my own to get a better handle on how it works.