How to compare SQLite datetime to C# string using Linq

Question

I'm trying to Compare the SQLITE table value GameDate with a C# string, tempDate using a Linq statement. I'm getting an error on the "var list = db.Table()" line.

Since I can't get the Linq line to work. I have just selected all the rows of the table and use a for loop to match. (it doesn't work either)

This is the Linq statement I'm trying to use.

// var list = db.Table().Where(n => n.GameDate== Convert.ToDateTime(tempDate)).ToList();

Here is my code:

        int recCtr = 0;
        var root = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
        var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "BaseBallOU.db");
        List<string> myCollection = new List<string>();

        foreach (Object obj in GameDateListBox.Items)
        {
            string tempDate= obj.ToString();
            using (var db = new SQLite.SQLiteConnection(dbPath))
            {
                try
                {

                 //   var list = db.Table<Results>().Where(DateTime.Compare(GameDate.Value.Date, tempDate) == 0);
                 //   var list = db.Table<Results>().Where(n => n.GameDate== Convert.ToDateTime(tempDate)).ToList();
                       var list = db.Table<Results>().ToList();
                       foreach (var item in list)
                       {
                           recCtr++;
                           if (item.GameDate.ToString("d") == tempDate.ToString())
                           {
                               myCollection.Add(item.GameDate.ToString());
                           }
                       }
                }
                catch { }

            }
        }

TIA, any help appreciated.

You should include the error message you are getting to increase the chances for a relevant answer. "An error" and "doesn't work" is not very descriptive. — C.Evenhuis

Farhad Jabiyev Farhad Jabiyev · Accepted Answer · 2015-03-11T19:12:21

EF can't parse Convert.ToDateTime to SQL. Instead of that, you can declare DateTime variable outside of the query.

DateTime dt = Convert.ToDateTime(tempDate);
var list = db.Table().Where(n => n.GameDate == dt).ToList();

Also, you may need to compare only Date() part of the DateTime. Then you need to use on of canonical functions like EntityFunctions.TruncateTime():

DateTime dt = Convert.ToDateTime(tempDate);
var list = db.Table().Where(n => EntityFunctions.TruncateTime(n.GameDate) == dt.Date).ToList();

How to compare SQLite datetime to C# string using Linq

1 Answers