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.
