How to use a DateTime in a where clause in Sqlite.Net

Question

I am writing a mobile application which uses Sqlite.Net I am trying to filter my Table using a DateTime property and LINQ to SQL

I have the following piece of code:

 var validDates = Database.Connection.Table<Dates>()
        .Where(x => x.StartDate <= DateTime.Today && x.EndDate >= DateTime.Today)
       .ToList();

But this Throws an Object Reference Not Set to the instance of an object And the stacktrace seems to suggest the error is in the Sqlite.Net library which leads me to beleive I am not using DateTimes correctly.

Warning: Unhandled exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object. 09-28 13:01:24.293 W/Xamarin.Insights(31238): at SQLite.Net.TableQuery1[T].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1[T] queryArgs) [0x00613] in <8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293 W/Xamarin.Insights(31238): at SQLite.Net.TableQuery1[T].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1[T] queryArgs) [0x0064b] in <8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293 W/Xamarin.Insights(31238): at SQLite.Net.TableQuery1[T].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1[T] queryArgs) [0x00027] in <8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293 W/Xamarin.Insights(31238): at SQLite.Net.TableQuery1[T].CompileExpr (System.Linq.Expressions.Expression expr, System.Collections.Generic.List1[T] queryArgs) [0x00027] in <8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293 W/Xamarin.Insights(31238): at SQLite.Net.TableQuery1[T].GenerateCommand (System.String selectionList) [0x0006d] in <8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293 W/Xamarin.Insights(31238): at SQLite.Net.TableQuery1[T].GetEnumerator () [0x00008] in <8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293 W/Xamarin.Insights(31238): at System.Collections.Generic.List1[T]..ctor (System.Collections.Generic.IEnumerable1[T] collection) [0x00073] in /Users/builder/data/lanes/3819/c1d1c79c/source/mono/mcs/class/referencesource/mscorlib/system/collections/generic/list.cs:98 09-28 13:01:24.293 W/Xamarin.Insights(31238): at System.Linq.Enumerable.ToList[TSource] (System.Collections.Generic.IEnumerable`1[T] source) [0x00011] in /Users/builder/data/lanes/3819/c1d1c79c/source/mono/mcs/class/referencesource/System.Core/System/Linq/Enumerable.cs:861

Doing it as a sql statemnet like this works:

 var query = string.Format("Select * from [Dates.Dates] where StartDate<=date('{0:yyyy-MM-dd}') and EndDate>=date('{1:yyyy-MM-dd}')", DateTime.Today, DateTime.Today);

But is there a way to do a DateTime query using LINQ to SQL?

Here is the source and method it is erroring in

Extra Info

My StoreDateTimeAsTicks setting is set to True

And My definitions for StartDate and EndDate are both DateTimes

My Dates class looks like this:

[Table("Dates.Dates")]
public class Dates : BaseModel
{
    public Dates()
    {
        //Don't fire notifications by default, since
        //they make editing the properties difficult.
        this.NotifyIfPropertiesChange = false;
    }

    [PrimaryKey]
    [NotNull]
    [AutoIncrement, Column("Id")]
    public int Id 
    { 
        get { return Id_private; }
        set { SetProperty(Id_private, value, (val) => { Id_private = val; }, Id_PropertyName); }
    }
    public static string Id_PropertyName = "Id";
    private int Id_private;

    [NotNull]
    [Column("Name")]
    public string Name 
    { 
        get { return Name_private; }
        set { SetProperty(Name_private, value, (val) => { Name_private = val; }, Name_PropertyName); }
    }
    public static string Name_PropertyName = "Name";
    private string Name_private;

    [NotNull]
    [Column("StartDate")]

    // The actual column definition, as seen in SQLite
    public string StartDate_raw { get; set; }

    public static string StartDate_PropertyName = "StartDate";

    // A helper definition that will not be saved to SQLite directly.
    // This property reads and writes to the _raw property.
    [Ignore]
    public DateTime StartDate { 
        // Watch out for time zones, as they are not encoded into
        // the database. Here, I make no assumptions about time
        // zones.
        get { return StartDate_raw != null ? DateTime.Parse(StartDate_raw) : StartDate = DateTime.Now; }
        set { SetProperty(StartDate_raw, StartDate_ConvertToString(value), (val) => { StartDate_raw = val; }, StartDate_PropertyName); }
    }

    // This static method is helpful when you need to query
    // on the raw value.
    public static string StartDate_ConvertToString(DateTime date)
    {    
        return date.ToString("yyyy-MM-dd");     
    }           

    [NotNull]
    [Column("EndDate")]

    // The actual column definition, as seen in SQLite
    public string EndDate_raw { get; set; }

    public static string EndDate_PropertyName = "EndDate";

    // A helper definition that will not be saved to SQLite directly.
    // This property reads and writes to the _raw property.
    [Ignore]
    public DateTime EndDate { 
        // Watch out for time zones, as they are not encoded into
        // the database. Here, I make no assumptions about time
        // zones.
        get { return EndDate_raw != null ? DateTime.Parse(EndDate_raw) : EndDate = DateTime.Now; }
        set { SetProperty(EndDate_raw, EndDate_ConvertToString(value), (val) => { EndDate_raw = val; }, EndDate_PropertyName); }
    }

    // This static method is helpful when you need to query
    // on the raw value.
    public static string EndDate_ConvertToString(DateTime date)
    {    
        return date.ToString("yyyy-MM-dd");     
    }
}

Answer 1

So the problem was that my StartDate and EndDate properties in my class AREN'T the columns. They are just wrappers for the real StartDate_Raw and EndDate_Raw properties. Which are strings.

Because the Raw columns were strings this meant I also can't filter the database using LINQ to SQL using string dates, so I was pretty stumped.

The limitation that my dates were stored as strings is a limitation of some third party software I am using called Zumero. If I hadn't been using this I would have just changed my table schema to be int instead.

So what I ended up doing was chaning all my DateTime columns in my SQL database to be Ticks and stored as Ints this meant I could change my string DateTime property to int and use the LINQ to SQL like so:

var validDates = Database.Connection.Table<Dates>()
        .Where(x => x.StartDate_Raw <= DateTime.Today && x.EndDate_Raw >= DateTime.Today)
       .ToList();