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].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
