sqlite - 日期比较时出现错误 : System. NotSupportedException

Question

因此，我尝试在 UserDatabse.cs 类中使用此方法检索特定日期的“Sugar”类的实例:

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

*请记住，应用程序目前没有任何 Sugar 实例，因此日期比较是在空日期和实际日期之间进行的。我认为这是导致错误的原因，任何解决方案将不胜感激。

在另一个类中调用此方法是这样的:

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

Sugar.cs 类定义如下:

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

现在得到的错误如下:

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 atSystem.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 .....

错误发生在该特定行:

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

我哪里出错了？

Answer 1

这部分表达式无法翻译为 SQL。

mi => mi.Time.Date

您可以尝试使用:

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.