QueryOver With Custom Projection and Query Parameter

Question

I've defined a query in a class with a property, but am trying to build a fairly complex query using the property and have run into NHibernate telling me that it could not resolve property: DueDate.

My Query class looks like this:

public class SomeQuery {
  public DateTime DueDate { get; private set; }
  public SomeQuery(DateTime dueDate) {
    DueDate = dueDate;
  }

  public QueryOver GetQueryOver() {

    PrimaryObject po = null;
    SubObject so = null;

    return QueryOver.Of<PrimaryObject>(() => po)
      .JoinAlias(() => so.SubObjects, () => so)
      .Where(
        Restrictions.Le(
          DateProjections.DateDiff("d", () so.Value, () = DueDate), 
          0
        )
      );
  }
}

I've implemented the DateProjections Class exactly as described in Andrew Whitaker's blog QueryOver Series - Part 7: Using SQL Functions

The contents of the PrimaryObject and SubObject aren't really important to the example except in the following:

public class PrimaryObject {
   public virtual Guid Id { get; set; }
   public List<SubObject> Implementations { get; set; }
}

public class SubObject {
  public virtual Guid Id { get; set; }
  public virtual string Value { get; set; }
}

For Mappings, you can assume that these fields are mapped to the database in sensible ways, as I don't feel like that is where the issue is.

When I try to use this query in a test, like the following:

var testDate = new DateTime(2015, 06, 01);
IEnumerable<PrimaryObject> result = repository.FindAll(new SomeQuery(testDate));

I get a NHibernate.QueryException:

NHibernate.QueryException : could not resolve property: DueDate of: PrimaryObject

Clearly, I've got an unmapped property, and that is causing the projection to have heartburn.

Looking for a minimal ceremony solution to getting the DueDate mapped. I've looked at Andrew's examples in QueryOver Series - Part 9: Extending QueryOver to Use Custom Methods and Properties, but it felt like a lot of ceremony.

I've also googled for solutions, but my google foo failed me..

Suggestions? Solutions?

Answer 1

The DateDiff implementation on the blog is assuming you wish to calculate the difference between database fields. This isn't what you want: you want to compare one database field with a constant.

You'll have to refactor the set of DateProjections methods to allow you to pass a constant as a parameter:

public static class DateProjections
{
    private const string DateDiffFormat = "datediff({0}, ?1, ?2)";


     // Here's the overload you need
    public static IProjection DateDiff
                  (
                    string datepart,
                    Expression<Func<object>> startDate,
                    DateTime endDate
                  )
   {
         return DateDiff(
                           datePart,
                           Projections.Property(startDate),
                           Projections.Constant(endDate)
                        );
   }        
    // Keeping Andrew Whitaker's original signature
    public static IProjection DateDiff
                             (
                                string datepart, 
                                Expression<Func<object>> startDate,
                                Expression<Func<object>> endDate
                             )
    {
        return DateDiff(
                         datePart,
                         Projections.Property(startDate),
                         Projections.Property(endDate)
                       );
    }
    // Added a function that's shared by 
    // all of the overloads
    public static IProjection DateDiff(
             string datepart,
             IProjection startDate,
             IProjection endDate)
    {
        // Build the function template based on the date part.
        string functionTemplate = string.Format(DateDiffFormat, datepart);

        return Projections.SqlFunction(
            new SQLFunctionTemplate(NHibernateUtil.Int32, functionTemplate),
            NHibernateUtil.Int32,
            startDate,
            endDate);
    }
}

Now you can invoke it like so:

public QueryOver GetQueryOver() {

   PrimaryObject po = null;
   SubObject so = null;

   return QueryOver.Of<PrimaryObject>(() => po)
     .JoinAlias(() => so.SubObjects, () => so)
     .Where(
        Restrictions.Le(
          DateProjections.DateDiff("d", () => so.Value, DueDate), 
          0
        )
  );
}