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");     
    }
}

@mybirthname No, its not duplicate. It has entirely different context. — Shaharyar, Sep 28 '16 at 12:47
Have you tried to set DateTime.Today into a variable? There are some LINQ providers that doesn't support functions (or behavior like a function) into the LINQ statements. — ADIMO, Sep 28 '16 at 13:12
@HenkHolterman I've updated the question with the relevant info — JKennedy, Sep 28 '16 at 13:38
That does make a lot of difference. Your actual Db columns are String, not DateTime. — H H, Sep 28 '16 at 13:44
@HenkHolterman `Sqlite.Net` represents a `DateTime` as a `string` there is no such thing as a `DateTime` in `Sqlite.Net` see [here](https://www.sqlite.org/datatype3.html) — JKennedy, Sep 28 '16 at 13:46
No but sqlite.net can store it as ticks. And the query engine knows how to deal with that (in combination with DateTime) — H H, Sep 28 '16 at 13:50

score 2 · Answer 1 · answered Sep 28 '16 at 13:15

2

Try this:

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

answered Sep 28 '16 at 13:15

Shaharyar

12,254
4
46
66

have you tried this out? It is still erroring at my end – JKennedy Sep 28 '16 at 13:18
No I didn't. Does your simple query work `Database.Connection.Table()` without this datetime condition? – Shaharyar Sep 28 '16 at 13:20
yes it returns the whole table. I can't help but feel it may be linked with the fact that Sqlite stores `DateTimes` as strings and not actual `DateTimes` – JKennedy Sep 28 '16 at 13:24
What is the sql query this linq query generates? – Shaharyar Sep 28 '16 at 13:26
Select * from [Dates]. but the one in this answer doesnt generate sql it still just crashes before it reaches that – JKennedy Sep 28 '16 at 13:27

score 1 · Accepted Answer · answered Oct 31 '16 at 16:11

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();

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

2 Answers2