Problem with query

Hi,

I'm having an issue with a query and so far I'm not sure if I'm doing something wrong, or if there's a bug, or if I'm just trying to do something that can't be done in the way I'm trying to do it.

List<Section> allSectionsWithinPublishDate =
    UnitOfWork.Query<Section>().WithAggregate("BoardSectionsQuestionsMessagesDetails").Where(
        s => s.BoardId == boardId && s.PublishDate < DateTime.UtcNow).
            OrderBy(s => s.Position).ToList();

foreach (Section s in allSectionsWithinPublishDate)
{
    //do stuff here
}

where the definition of the PublishDate property of the Section class is:

public DateTime PublishDate
{
    get { return _publishDate != null ?
    (DateTime) _publishDate : Board.StartDate;}
    set { Set(ref _publishDate, value);}
}

In my example I have 6 Sections which meet the criteria of the query (_pulishDate is null and Board.StartDate is a valid DateTime in the past), but the allSectionsWithinPublishDate list is empty after the query is executed. If I change the code to read:

List<Section> allSectionsWithinPublishDate =
    UnitOfWork.Query<Section>().WithAggregate("BoardSectionsQuestionsMessagesDetails").Where(
        s => s.BoardId == boardId).
            OrderBy(s => s.Position).ToList();

foreach (Section s in allSectionsWithinPublishDate.Where(s => s.PublishDate <= DateTime.UtcNow))
{
    //do stuff here
}

then the foreach loop repeats 6 times as expected.

So is there a reason why I can't use "s.PublishDate < DateTime.UtcNow" in the Where clause? Is it because PublishDate is not a wrapper for a simple field, but accesses another object?

Any help appreciated.

Thanks Andrew

Hi Andrew,

Having that logic in the property getter means that it will be hidden away from being interpreted as part of a LINQ expression tree, so there is no way for it to be interpreted as part of the server side query. However we do offer a facility for working around this and returning back an expression which represents that property which will be used by our LINQ provider.

Have a read about how you can do this here: http://www.mindscapehq.com/blog/index.php/2010/09/14/ninja-domain-properties-in-lightspeed/

Jeremy

Thanks Jeremy,

I'll have a read through that article and hopefully it'll sort out my issue.

Thanks Andrew

Hi Jeremy,

I'm working on this issue as well and currently we are trying to use in the Section class for our expression

public DateTime PublishDate { get { return _publishDate != null ? (DateTime) _publishDate : Board.StartDate;} set { Set(ref _publishDate, value);} }

private static readonly Expression> PublishDateExpression = s => s.publishDate != null ? (DateTime)s.publishDate : (DateTime)s.Board.StartDate;

This seems to cause a null reference exception whenever the line used by Andy above was called. Could you shed some light on why this might be happening?

Hi Andrew and Jacob,

The core problem is that LightSpeed is getting confused between the persistent PublishDate field (which maps directly to a column) and the 'decorated' PublishDate expression which contains business logic (and therefore maps to a SQL expression). There is also an additional problem with our handling of traversals within functions (which comes into play because your conditional operator resolves to a CASE function).

We will continue working on this to see if we can improve our handling of this situation, but for the time being the best solution is to tweak your API and the backing expression, along the following lines:

private static readonly Expression<Func<Section, DateTime, bool>> IsPublishDateBeforeExpression =
  (s, d) => s.PublishDate < d || s.PublishDate == null && s.Board.StartDate < d;

public bool IsPublishDateBefore(DateTime d)
{
  throw new NotImplementedException();
}

Using the || operator instead of the conditional avoids the issue with traversals in function, and using a different name avoids the confusion between the PublishDate column and the PublishDate business logic.

You can then rewrite your query using this API:

UnitOfWork.Query<Section>().Where(s => s.IsPublishDateBefore(DateTime.UtcNow)).ToList();

and this works for me.

Obviously the downside of this API is that it reduces the flexibility of queries on the publish date but given that complex queries on the 'decorated' publish date could expand into very complex SQL, limiting the scope for such queries may be no bad thing anyway! However as I said we'll continue to investigate whether we can improve our handling of this case.