NHibernate criteria query help

Question

Hello

Given the following tables, I am trying to return all Allocations for a given Resource's that fall between a given range of dates using a criteria query:

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)
)

public Resource FetchFor(Resource resource, DateRange range) {
    var allocations = _session.CreateCriteria<Resource>()
            .CreateCriteria("Allocations")
                .Add(Restrictions.Between("EndTime", (DateTime)range.Start, (DateTime)range.End))
            .List<Allocation>();

        if (allocations.Count() > 0) 
            resource.ReplaceAllocations(allocations);

        return resource;
    }

I get this exception when I run this:

failed: NHibernate.QueryException : could not resolve property: EndTime of: Domain.Model.Allocations.Allocation

The model is mapped and I can save a Resource and it's associated Allocations without trouble, as I am doing to test this Fetch query. An example line of NHib sql: NHibernate: INSERT INTO Allocations (StartTime, EndTime, PostingTime, ResourceId, ActivityBaseId) VALUES (@p0, @p1, @p2, @p3, @p4); select last_insert_rowid();@p0 = 1/28/2010 12:00:00 AM, @p1 = 1/28/2010 1:00:00 AM, @p2 = NULL, @p3 = 1, @p4 = 4

The exception message is confusing since NHib does clearly understand how to map Allocation.EndTime. Please:
1) help trouble shoot / clean up the query in this post, and
2) point out any favorite resources for learning nhib queries, especially if there are examples there.

As an aside I do realize I am not using the Resource param in the example shown, as I pretty much (mis?)applied the code from an example on Ayende's posting. If I can get this join query working I will turn it into a subquery for performance.

Thanks!
Berryl

=== NHib Mapping =====

<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, Smack.ConstructionAdmin.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>

Answer 1

I've been trying to duplicate your issue, but was unsuccessful. Since you're not using resource in your query, could you try to simplify things, such as:

var allocations = _session.CreateCriteria<Allocation>()  
            .Add(Restrictions.Between("EndTime", (DateTime)range.Start, (DateTime)range.End))
        .List<Allocation>(); 

I have done a few join queries, I can provide the following example, I hope this helps:

        public IList<ItemOrder> GetItemOrderByCriteria(int? itemNumber, int? warehouseNumber, DateTime? orderPickDate, string orderStoreNum, string statusCode)
    {
        try
        {
            NHibernate.ICriteria criteria = NHibernateSession.CreateCriteria(typeof(Core.SuggestedOrders.ItemOrder));

            if (itemNumber.HasValue)
                criteria.CreateCriteria("Item", "Item").Add(Expression.Eq("Item.ItemNumber", itemNumber.Value));

            if (warehouseNumber.HasValue)
                criteria.CreateCriteria("Warehouse", "Warehouse").Add(Expression.Eq("Warehouse.WarehouseNumber", warehouseNumber));

            if (!String.IsNullOrEmpty(orderStoreNum))
                criteria.Add(Expression.Eq("OrdStoreNum", orderStoreNum));

            if (!String.IsNullOrEmpty(statusCode))
                criteria.Add(Expression.Eq("StatusCode", statusCode));

            if (orderPickDate.HasValue)
            {
                DateTime minPickDate = new DateTime(orderPickDate.Value.Year, orderPickDate.Value.Month, orderPickDate.Value.Day, 0,0,0);
                DateTime maxPickDate = new DateTime(orderPickDate.Value.Year, orderPickDate.Value.Month, orderPickDate.Value.Day, 23,59,59);

                criteria.Add(Expression.Between("OrdPickDate", minPickDate, maxPickDate));
            }

            return criteria.List<Core.SuggestedOrders.ItemOrder>();
        }
        catch (NHibernate.HibernateException he)
        {
            DataAccessException dae = new DataAccessException("NHibernate Exception", he);
            throw dae;
        }

    }

Answer 2

It seems like you don't have a mapped property of Allocation called EndTime so its not surprising you get this error. The EndTime column is mapped through the custom type TimeRange so you would expect to query on that.

A sample of Allocation.cs and the customer TimeRange would probably also help understand the problem.