NHibernate "SELECT ... FROM (SELECT ..." Criteria

Question

I have a SQL query I'm having trouble creating using NHibernate Criteria:

SELECT ID, COLA, COLB, COLC
FROM table
WHERE COLC='something' AND ID IN (SELECT ID FROM (SELECT MAX(ID) as ID, COLA FROM table WHERE COLB='something' GROUP BY COLA) subquery)
ORDER BY ID DESC

I originally had this slightly simpler query:

SELECT ID, COLA, COLB, COLC
FROM table
WHERE COLC='something' AND ID IN (SELECT MAX(ID) FROM table WHERE COLB='something' GROUP BY COLA)
ORDER BY ID DESC

However, with NHibernate, if I use a "GROUP BY", it automatically adds the field to the SELECT statement, and I have no way of stopping it (as far as I can find).

Basically, I need to find the latest record grouped by some arbitrary column (in this example, "COLA"). I'm selecting the maximum ID to try to get the latest record (though this could be something else, like "MAX(UPDATED)"). After getting the set of latest records, I'm further filtering them ("WHERE COLC='something'"), and selecting the columns I need in the result.

If there's a better way to get the same results, I'd be glad to hear it. My SQL skills are mediocre, at best.

In NHibernate, I could get the two queries right, but the piece in the middle - "SELECT ID FROM", wouldn't work.

The main query:

DetachedCriteria.For<table>()
    .Add<table>(x => x.COLC == "something")
    .Add(LambdaSubquery.Property<table>(x => x.ID).In(subquery));

And the subquery:

DetachedCriteria.For<table>()
    .Add<table>(x => x.COLB == "something")
    .SetProjection(Projections.ProjectionList()
        .Add(LambdaProjection.Max<table>(x => x.ID))
        .Add(LambdaProjection.GroupProperty<table>(x => x.COLA)));

The subquery criteria puts "COLA" in the select list (because of the GroupProperty), so it's unusable on its own, and that's why I need to figure out how to do the "SELECT ID FROM (SELECT ..." in the criteria. When combined, they produce the following invalid SQL (because the subquery returns more than one column):

SELECT ID, COLA, COLB, COLC
FROM table
WHERE COLC='something' AND ID IN (SELECT MAX(ID), COLA FROM table WHERE COLB='something' GROUP BY COLA)
ORDER BY ID DESC

Edit: It might also help to see the kind of data and results I want:

ID  COLA  COLB          COLC
1   1     someone       someother
2   1     something     someone     (Matches subquery, but not the max. ID)
3   1     something     something   (Matches subquery and main query)
4   2     someone       something
5   2     something     someother   (Only matches subquery)
6   3     someone       someother

The result I want here are the maximum ID for a given set of "COLA"s, where "COLB" matches "something", so I'd want the subquery to return {3, 5}. In the end, the query would only return the record for ID 3 (the outer WHERE clause weeds out the 5 because COLC is wrong). The actual data in COLB and COLC is irrelevant - I'm just using that to further filter the results.

I think, at the core of it, I want the latest record (max ID) for each set of COLA

SELECT ID, COLA, COLB
FROM table
WHERE ID IN (SELECT MAX(ID) FROM table GROUP BY COLA)
ORDER BY ID DESC

Answer 1

So assuming your looking for the record(s) that have the Max ID with the value from COLA and also has COLB in it with the corresponding MAX ID you could use something like this.

var test =
                ActiveRecordLinq.AsQueryable<table>().Where(
                    y =>
                    y.COLB == "Something" &&
                    y.ID == (from x in ActiveRecordLinq.AsQueryable<table>()
                                   where x.COLA == "Something"
                                   select x).Max(z => z.ID))

I tried this using some of my object and test data and it seemed to work. Let me know how it goes.

EDIT

For the list of max ID with COLA you can use this query. First I'll show you the one I was using for testing then what yours should probably look like.

I have an email group object that contains a headline and a TXDX object which has the ID I was searching on.

var maxes = (from x in ActiveRecordLinq.AsQueryable<Email_Group>()
                     group x by x.Headline
                     into g
                     select new {COLA = g.Key, MaxID = g.Max(z => z.TXDX.TXDX_ID)});

Yours:

var maxes = (from x in ActiveRecordLinq.AsQueryable<table>()
                     group x by x.COLA
                     into g
                     select new {COLA = g.Key, MaxID = g.Max(z => z.ID)});

I'm still working on the whole query. Trying to get it done in one call. I will post once I get it but it is Friday and I'm doing this in between work.

Answer 2

I found an alternative solution since I couldn't figure out how to get the right results using the criteria API in NHibernate - use a view instead.

The view simply uses the query I had originally wanted (without some of the extra filtering):

SELECT ID, COLA, COLB, COLC, ...
FROM table
WHERE ID IN
    (SELECT MAX(ID)
     FROM table
     GROUP BY COLA)

Then, in NHibernate, I mapped the view just like a regular table (except I made it immutable):

<class name="View" table="View" lazy="false" mutable="false">
    <id name="ID" type="Int32">
        <generator class="assigned"/>
    </id>
    <property name="COLA" type="String" length="100">
        <column name="COLA" />
    </property>
    <property name="COLB" type="String" length="100">
        <column name="COLB" />
    </property>
    <property name="COLC" type="String" length="100">
        <column name="COLC" />
    </property>
    <!-- Other fields -->
</class>

And created the class to map to:

public class View
{
    public virtual int ID { get; set; }
    public virtual string COLA { get; set; }
    public virtual string COLB { get; set; }
    public virtual string COLC { get; set; }
    // Other fields
}

And finally, created a query for the view:

DetachedCriteria.For<View>()
    .Add<View>(x => x.COLB == "something")
    .Add<View>(x => x.COLC == "something")
    // Any other filtering criteria
    .AddOrder<View>(x => x.COLA, Order.Desc);

I'd have rather used a single criteria query in NHibernate, but this got the job done.