user1 user1 - 1 month ago 36
C# Question

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

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.TableQuery
1[T].CompileExpr
(System.Linq.Expressions.Expression expr,
System.Collections.Generic.List
1[T] queryArgs) [0x00613] in
<8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293
W/Xamarin.Insights(31238): at SQLite.Net.TableQuery
1[T].CompileExpr
(System.Linq.Expressions.Expression expr,
System.Collections.Generic.List
1[T] queryArgs) [0x0064b] in
<8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293
W/Xamarin.Insights(31238): at SQLite.Net.TableQuery
1[T].CompileExpr
(System.Linq.Expressions.Expression expr,
System.Collections.Generic.List
1[T] queryArgs) [0x00027] in
<8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293
W/Xamarin.Insights(31238): at SQLite.Net.TableQuery
1[T].CompileExpr
(System.Linq.Expressions.Expression expr,
System.Collections.Generic.List
1[T] queryArgs) [0x00027] in
<8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293
W/Xamarin.Insights(31238): at
SQLite.Net.TableQuery
1[T].GenerateCommand (System.String
selectionList) [0x0006d] in <8f2bb39aeff94a30a8628064be9c7efe>:0
09-28 13:01:24.293 W/Xamarin.Insights(31238): at
SQLite.Net.TableQuery
1[T].GetEnumerator () [0x00008] in
<8f2bb39aeff94a30a8628064be9c7efe>:0 09-28 13:01:24.293
W/Xamarin.Insights(31238): at
System.Collections.Generic.List
1[T]..ctor
(System.Collections.Generic.IEnumerable
1[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

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