c# - SQLite and LINQ: 'Member access failed to compile expression' (WHERE Condition)

Question

I have a Xamarin.Forms page with a ListView that on appearing should be populated with the 5 most recent product dates where the date is today's date. I am using an SQLite database to store the date and time at which a new product is entered into the inventory.

I have a problem with this query in Database.cs:

return _database.Table<Product>().OrderByDescending(x => x.ProductDateTime)
       .Where(y => y.ProductDateTime.Date == DateTime.Today).Take(5).ToListAsync();

This part of the query is causing an error:

Where(y => y.ProductDateTime.Date == DateTime.Today)

System.NotSupportedException: 'Member access failed to compile expression'

I have tried to solve this by trying to use ToList() / ToListAsync().

var item = _database.Table<Product>().ToListAsync().OrderByDescending(x => x.ProductDateTime).Where(y => y.ProductDateTime.Date == DateTime.Today).Take(5);

return item.ToListAsync();

However, this results in a different error:

Error CS1061 'Task<List>' does not contain a definition for 'OrderByDescending' and no accessible extension method 'OrderByDescending' accepting a first argument of type 'Task<List>' could be found (are you missing a using directive or an assembly reference?)

Product.cs:

public class Product
{ 
     [PrimaryKey, AutoIncrement]
     public int ID { get; set; }
     public DateTime ProductDateTime { get; set; }
}

Database.cs:

public class Database
{
     readonly SQLiteAsyncConnection _database;

     public Database(string dbPath)
     {
            _database = new SQLiteAsyncConnection(dbPath);
            _database.CreateTableAsync<Product>().Wait();
     }

     public Task<List<Product>> GetProductAsync()
     {

          return _database.Table<Product>().OrderByDescending(x => x.ProductDateTime).Where(y => y.ProductDateTime.Date == DateTime.Today).Take(5).ToListAsync();

     }

     public Task<int> SaveProductAsync(Product product)
     {
            return _database.InsertAsync(product);
     }
}

App.xaml.cs:

static Database database;

public static Database Database
{
            get
            {
                if (database == null)
                {
                    database = new Database(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "product.db3"));
                }
                return database;
            }
}

How can I fix this issue? Thank you.

question from:https://stackoverflow.com/questions/65928825/sqlite-and-linq-member-access-failed-to-compile-expression-where-condition

Answer 1

DateTime fields in the database don't have an eqivalent of DateTime.Date property in .NET. I would try something like this:

var yesterday = DateTime.Today.AddDays(-1);
var tomorrow = DateTime.Today.AddDays(1); // <-- omit this, if your database doesn't have any rows with ProductDateTime in the future
....
Where(y => y.ProductDateTime > yesterday &&  y.ProductDateTime < tomorrow)