Getting Error: System.NotSupportedException when Date Comparison

Issue

So i am trying to retrieve an instance of the class "Sugar" for a specific date using this method in my UserDatabse.cs class:

public List<Sugar> GetSugarDate(DateTime dt)  
{  
    var bld = database.Table<Sugar>().Where(mi => mi.Time.Date == dt.Date).ToList();  
    return bld;  
}  

*Keep in mind that the app doesn't have any instance of Sugar at the moment so the Date comparison is between a null and an actual date. I think that is causing the error, any solution would be appreciated.

The call to this method is made like this in another class:

DateTime Time = DateTime.Now;

ObservableCollection<Image> Sugar_Count = new ObservableCollection<Image>();
Image s = new Image();
s.Source = "drawable/soda.png";
var xa = App.Database.GetSugarDate(Time);

The class Sugar.cs is defined as follows:

public class Sugar
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public DateTime Time { get; set; }
    public int DrinkCount { get; set; }

    public Sugar()
    {
        Time = DateTime.Now;
        DrinkCount = 0;
    }
}  

Now the error is get is as follows:

System.NotSupportedException: Member access failed to compile expression at SQLite.TableQuery'1[T].CompileExpr(System.Linq.Expressions.Expression expr, System.Collections.Generic.List'1[T]queryArgs)[0x006cc]in <9b4aaa861238418bec74a2ddde70f09>:0 at SQLite.TableQuery'1[T].CompileExpr(System.Linq.Expressions.Expression expr, System.Collections.Generic.List'1[T]queryArgs)[0x0009d]in <9b4aaa861238418bec74a2ddde70f09>:0 at SQLite.TableQuery'1[T].CompileExpr(System.Linq.Expressions.Expression expr, System.Collections.Generic.List'1[T]queryArgs)[0x0005f]in <9b4aaa861238418bec74a2ddde70f09>:0 at SQLite.TableQuery'1[T].CompileExpr(System.Linq.Expressions.Expression expr, System.Collections.Generic.List'1[T]queryArgs)[0x00008]in <9b4aaa861238418bec74a2ddde70f09>:0 at System.Collections.Generic.List'1[T]..ctor(System.Collections.Generic.IEnumerable'1[T]collection)[0x00062] in /Users/builder/jenkins/workspace/xamarin-android/external/mono/mcs/class/referencesource/generic/list.cs:98 .....

The error occurs at this specific line:

var bld = database.Table<Sugar>().Where(mi => mi.Time.Date == dt.Date).ToList();

Where am i going wrong?

Solution

This part of the expression can not be translated to SQL.

mi => mi.Time.Date

You can try using:

var bld = database.Table<Sugar>().ToList().Where(mi => mi.Time.Date == dt.Date).ToList();

Accessing to the Date part of a DateTime column (mi.Time.Date) can not be translated to SQL using Linq, for this reason, your statement failed initially. So, using ToList fetches all records in memory allowing us to query with Linq to Object method not Linq to SQLite.

Answered By - Jesus Angulo