Changing HQL to Linq?

Question

I have a situation where I was using a Named HQL Query, but now that my DAL has changed, I don't know how to re-do the named query in Linq. I have an entity called a HolterTest, which will be read in. I need to create a WorkItem that will contain that HolterTest, so in order to do that, I need to find all of the existing HolterTests that don't yet have a WorkItem associated with them.

Here was my original HQL named query:

<query name="Get.HolterTests.Without.WorkItems" >
<![CDATA[ from HolterTest as ht
          where not exists (from WorkItem as wi
          where wi.HolterTest.ID = ht.ID) 
          and ht.RecordingStartDateTime == null]]>
</query>

Here is my NHibernate Mapping for the WorkItem entity:

<class name="WorkItem" table="WorkItems" where="" dynamic-update="true" dynamic-insert="true" >
<id name="ID" column="WorkItemID" type="Int32" >
  <generator class="identity"/>
</id>

<property name="Status"  type="AnsiString" />
<property name="IsCompleted"  />
<property name="IsStarted" />    
<property name="CompletedDateTime" type="DateTime" />
 <many-to-one name="HolterTest" lazy="false" class="HolterTest" column="HolterTestID" unique="true" not-null="true" cascade="save-update"/>

<bag name="WorkTasks" cascade="all-delete-orphan" inverse="true" lazy="false"  >
  <key column="WorkItemID" on-delete="cascade" />
  <one-to-many class="HolterManagementBackend.Domain.Models.WorkTask"/>
</bag>

Here is the Mapping file for the HolterTest object, where I have the one-to-one mapping.

  <class name="HolterTest" table="HolterTests" where="" dynamic-update="true" dynamic-insert="true">
<id name="ID" column="HolterTestID" type="Int32" >
  <generator class="identity"/>
</id>

<property name="MayoClinicNumber" type="AnsiString" />
<property name="HolterTestType" type="Int32" />
<property name="LastName"  type="AnsiString" />
<property name="RecordingStartDate"  type="DateTime" not-null="false" />
<property name="HookUpApptDate" type="DateTime" not-null="false" />
<property name="FollowupApptDate" type="DateTime" not-null="false" />
<property name="Room" type="AnsiString" />
<property name="HolterUnitSerial" type="AnsiString" />
<property name="HookupTechLanID" type="AnsiString" />
<property name="OrderingMDLanID" type="AnsiString" />
<property name="ReviewingMDLanID" type="AnsiString" />

<one-to-one name="WorkItem" lazy="false" class="WorkItem" property-ref="HolterTest" cascade="save-update" />

<many-to-one name="Location" class="Location" column="LocationID"  not-found="ignore" fetch="join" lazy="false"/>

In my rework of my DAL, I now have the abstract class Specification for using Linq:

namespace HolterManagementBackend.DAL.Contracts
{
public abstract class Specification<T>
  {
    public abstract Expression<Func<T, bool>> MatchingCriteria { get; }

    public T SatisfyingElementFrom(IQueryable<T> candidates)
    {
        return SatisfyingElementsFrom(candidates).Single();
    }

    public IQueryable<T> SatisfyingElementsFrom(IQueryable<T> candidates)
    {
        return candidates.Where(MatchingCriteria).AsQueryable();
    }

  }
}

This is used in the DAO object's FindAll method:

    /// <summary>
    /// finds all the objects which match the specification expression provided
    /// </summary>
    /// <param name="query">the expression to search on</param>
    /// <returns>all the objects that match the criteria</returns>
    [Transaction( ReadOnly = true)]
    public IList<T> FindAll(Specification<T> query)
    {
        return query.SatisfyingElementsFrom(SessionFactory.GetCurrentSession().Query<T>()).ToList();
    }

Here is a simpler Specification, to get all HolterTests for a certain region, with a HookupApptDate range.

public class GetHolterTestToHookUpByRegionID : Specification<HolterTest>
{
    private readonly int _days;
    private readonly int _regionId;

    public GetHolterTestToHookUpByRegionID(int regionId, int numberOfDays)
    {
        _days = numberOfDays;
        _regionId = regionId;
    }

    public override Expression<Func<HolterTest, bool>> MatchingCriteria
    {
        get
        {
            return x => x.HookUpApptDate <= Convert.ToDateTime(System.DateTime.Today.AddDays(_days))
                && x.Location.Region.ID == _regionId && x.RecordingStartDate == null;
        }
    }

}

What I don't know how to do is to write the Specification that includes the NotExists and the join. Any suggestions/ideas?

UPDATE: With the one-to-one, when I get all HolterTest objects where the RecordingStartDate is null, I can loop through and find the ones where WorkItem is null, but when I try to put x.WorkItem == null into the query Spec, I don't get anything back.

Answer 1

You would give HolterTest a collection property named WorkItems and check that it doesn't contain anything:

x.RecordingStartDate == null && !x.WorkItems.Any()