You wouldn't want to see how this can be done with criteria api. Tuna Toksöz Eternal sunshine of the open source mind.
http://devlicio.us/blogs/tuna_toksoz http://tunatoksoz.com http://twitter.com/tehlike On Wed, Sep 9, 2009 at 12:16 PM, Hitt <[email protected]> wrote: > > I just recently converted a rather simple webapp I created that was > using straight SQL/Datasets to NHibernate. I basically want to be > able to do a query like this (in Linq2NHibernate preferably, but > Criteria or HQL is fine) > > SELECT ID,ClientID,..., > (SELECT COUNT(*) FROM Clock WHERE ClientID=c.ID AND > Status='ON') AS ClocksUp > (SELECT COUNT(*) FROM Clocks WHERE ClientID=c.ID AND > Status='OFF') AS ClocksDown > FROM Client AS c > > I've tried the standard query, (which I figured wouldn't work as L2NH > specifically says it doesn't support subqueries in projections yet) > > from client session.Linq<Client> > select new { Client = client, ClocksUp = client.Clocks.Count(c=> > c.Status == "ON"), ClocksDown = client.Clocks.Count(c=> c.Status == > "OFF") } > > This gives an error saying it can't bind "Clocks.ID". > > So I want to be able to do a query equivalent to this that will fly in > L2NH, or with Criteria queries or HQL. I've made several attempts in > each and just can't get anything that works. > > Anyone have any ideas of how to go about this? > > Thanks. > > -------------------------------- Mapping files > below------------------------------------------ > Client > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default- > access="property" auto-import="true" default-cascade="none" default- > lazy="true"> > <class xmlns="urn:nhibernate-mapping-2.2" name="Domain.Client" > table="Client"> > <id name="ID" type="System.Int32"> > <column name="ID"/> > <generator class="identity"/> > </id> > <property name="Status" > type="FluentNHibernate.Mapping.GenericEnumMapper`1 > [[Domain.ClientStatus]], FluentNHibernate, Version=1.0.0.593, > Culture=neutral, PublicKeyToken=8aa435e3cb308880"> > <column name="Status"/> > </property> > <property name="ClientID" type="System.String"> > <column name="ClientID"/> > </property> > <property name="Updated" type="System.DateTime"> > <column name="Updated"/> > </property> > <property name="LastStatusUpdate" type="System.Nullable`1 > [[System.DateTime]]"> > <column name="LastStatusUpdate"/> > </property> > <property name="DataReaderStatus" type="System.Nullable`1 > [[System.Boolean]]"> > <column name="DataReaderStatus"/> > </property> > <property name="DataMoverStatus" type="System.Nullable`1 > [[System.Boolean]]"> > <column name="DataMoverStatus"/> > </property> > <bag inverse="true" name="Clocks"> > <key> > <column name="ClientID"/> > </key> > <one-to-many class="Domain.Clock"/> > </bag> > </class> > </hibernate-mapping> > > Clock: > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default- > access="property" auto-import="true" default-cascade="none" default- > lazy="true"> > <class xmlns="urn:nhibernate-mapping-2.2" name="Domain.Clock" > table="Clock"> > <id name="ID" type="System.Int32"> > <column name="ID"/> > <generator class="identity"/> > </id> > <property name="ClockID" type="System.Int32"> > <column name="ClockID"/> > </property> > <property name="Description" type="System.String"> > <column name="Description"/> > </property> > <property name="Status" type="System.String"> > <column name="Status"/> > </property> > <property name="LastUpdate" type="System.DateTime"> > <column name="LastUpdate"/> > </property> > <many-to-one class="Domain.Client" name="Client"> > <column name="ClientID"/> > </many-to-one> > </class> > </hibernate-mapping> > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
