Try Now
More in this section

Forums / Developing with Sitefinity / Filter Expression with (x=1 OR x is null) format?

Filter Expression with (x=1 OR x is null) format?

2 posts, 0 answered
  1. Jonathan
    Jonathan avatar
    1 posts
    22 Sep 2009
    11 Apr 2011
    Link to this post
    I've added these metaFields to my a Documents & Images (v3.7 build 2096) module:
    FromDate (DateTime)
    ToDate (DateTime)

    These fields are for storing effective dates for a document (i.e. "This document is valid from 1/1/2011 - 12/31/2011")  The fields are intended to be optional: A document with no ToDate is always valid.

    When displaying information from this module on my site I'd like to only show entries where "#Now" is between FromDate and ToDate OR where ToDate IS NULL OR where ToDate = "".  If I were constructing a SQL statement to achieve this, it would be: " ... WHERE isnull(FromDate, getdate()) >= getdate() AND isnull(ToDate, getdate() <= getdate())"

    I've got this FilterExpression: ParentID IN (4e4e997a-43ad-4e39-9aca-43c8c80b540a) AND FromDate <= "#Now" AND ToDate >= "#Now"
    It works, other than the case where FromDate or ToDate is not populated.

    How can I achieve the "or is null" logic I'm after?  SiteFinity seems to not like parenthesis in a FilterExpression and I can't find adequate documentation on this in the User Manual, Forums, Blog posts, etc.

    Further question: Is "ToDate IS NULL OR ToDate = """ redundant? Does blank = NULL?


  2. Ivan Dimitrov
    Ivan Dimitrov avatar
    16072 posts
    25 Oct 2016
    11 Apr 2011
    Link to this post
    Hello Jonathan,

    "OR" is not supported and it is not possible to use in with FilterExpression. The solution requires a custom control that inherits from the content view based control you use and there you can query the data with LINQ.

    Ivan Dimitrov
    the Telerik team

2 posts, 0 answered