Guys - thanks for the help! On Jan 27, 7:10 am, Fabio Maulo <[email protected]> wrote: > perhaps a SetInt32 and a SetDateTime would work a little bit better > > 2010/1/27 Jason Meckley <[email protected]> > > > > > something like: > > hql > > var hql = @"SELECT r.ResourceId, a.StartTime, a.EndTime > > FROM Resources r join r.Allocations a > > WHERE r.BusinessId = :business > > AND a.StartTime between :start and :end" > > > IList results = session > > .CreateQuery(hql) > > .SetParameter("business", businessId) > > .SetParameter("start", range.From) > > .SetParameter("end", range.To) > > .List(); > > > criteria > > IList results = session > > .CreateCriteria<Resource>() > > .CreateAlias("Allocations", "a") > > .Add(Restrictions.Eq("BusinessId", businessId) > > .Add(Restrictions.Between("a.StartTime", range.From, range.To) > > .SetProjection(Projections > > .List() > > .Add(Projections.Add("id")) > > .Add(Projections.Add("a.StartTime")) > > .Add(Projections.Add("a.EndTime")) > > ) > > .List(); > > > On Jan 26, 11:39 pm, Berryl Hesh <[email protected]> wrote: > > > Can someone please help me translate the SQL below into an NHib query? > > > Ideally both hql and criteria style? I'm just not grokking it yet > > > > Thanks, > > > Berryl > > > > ============ > > > > SELECT Resources.ResourceId, Allocations.StartTime, > > > Allocations.EndTime > > > FROM Resources INNER JOIN > > > Allocations ON Resources.ResourceId = > > > Allocations.ResourceId > > > WHERE (Resources.BusinessId = "009-67") AND ( > > > Allocations.StartTime >= > > CONVERT(DATETIME, '2010-01-01 00:00:00', > > > 102)) AND ( > > > Allocations.StartTime < CONVERT(DATETIME, > > '2010-02-01 00:00:00', > > > 102)) > > > > the repository interface I'm thinking of would be something like: > > > > public Resource FetchFor(string businessId, DateRange range) > > > > The DDL for the tables is: > > > > create table Resources ( > > > ResourceId integer, > > > ResourceName TEXT not null, > > > BusinessId TEXT not null, > > > OrganizationName TEXT not null, > > > primary key (ResourceId) > > > ) > > > > create table Allocations ( > > > AllocationId integer, > > > StartTime DATETIME not null, > > > EndTime DATETIME not null, > > > PostingTime DATETIME, > > > ResourceId INTEGER, > > > ActivityBaseId INTEGER, > > > primary key (AllocationId), > > > unique (StartTime, EndTime, ResourceId, ActivityBaseId) > > > ) > > > > And the mapping for Allocation id > > > > <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.Model.Allocations.Allocation, Domain, Version=1.0.0.0, > > > Culture=neutral, PublicKeyToken=null" table="Allocations"> > > > <id name="Id" type="System.Int32, mscorlib, Version=2.0.0.0, > > > Culture=neutral, PublicKeyToken=b77a5c561934e089" unsaved-value="0"> > > > <column name="AllocationId" /> > > > <generator class="identity" /> > > > </id> > > > <property name="TimeRange" > > > type="Data.UserTypes.AllocationTimeRangeUserType, Data, > > > Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"> > > > <column name="StartTime" not-null="true" unique- > > > key="DomainSignature" /> > > > <column name="EndTime" not-null="true" unique-key="DomainSignature" / > > > > </property> > > > <property name="PostingTime" type="System.Nullable`1[[System.DateTime, > > > mscorlib, Version=2.0.0.0, Culture=neutral, > > > PublicKeyToken=b77a5c561934e089]], mscorlib, Version=2.0.0.0, > > > Culture=neutral, PublicKeyToken=b77a5c561934e089"> > > > <column name="PostingTime" not-null="false" /> > > > </property> > > > <many-to-one class="Domain.Model.Resources.Resource, Domain, > > > Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" foreign- > > > key="Resource_FK" name="Resource"> > > > <column name="ResourceId" unique-key="DomainSignature" /> > > > </many-to-one> > > > <many-to-one class="Domain.Model.Activities.ActivityBase, Domain, > > > Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" foreign- > > > key="ActivityBase_FK" name="Activity"> > > > <column name="ActivityBaseId" unique-key="DomainSignature" /> > > > </many-to-one> > > > -- > > 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]<nhusers%[email protected]> > > . > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en. > > -- > Fabio Maulo
-- 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.
