SQLite.net table query throws NullReferenceException

Question

I'm using the following line of code to query some data from a given table in a SQLite database (platform is WP81 but I guess this doesn't matter here).

return await Connection.Table<WorkDay>().Where(wd => wd.Date >= @from && wd.Date <= to).ToListAsync().ConfigureAwait(false);

When I execute my code I get a NullReferenceException in the Where clause. When I remove the where condition everything works fine.

return await Connection.Table<WorkDay>().ToListAsync().ConfigureAwait(false);

In order to make sure that all entries in my table are valid and there is no null value in the Date column I used an SQL tool to look into the SQLite database.

As I can't debug lambda expressions I'm kind of stuck on how to find the issue here. My assumption is that something goes wrong due to the async handling.

Edit: Here is the exact stacktrace of the exception

{System.NullReferenceException: Object reference not set to an instance of an object.
   at SQLite.Net.TableQuery`1.CompileExpr(Expression expr, List`1 queryArgs)
   at SQLite.Net.TableQuery`1.CompileExpr(Expression expr, List`1 queryArgs)
   at SQLite.Net.TableQuery`1.CompileExpr(Expression expr, List`1 queryArgs)
   at SQLite.Net.TableQuery`1.GenerateCommand(String selectionList)
   at SQLite.Net.TableQuery`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at SQLite.Net.Async.AsyncTableQuery`1.<ToListAsync>b__0()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at TimeStamp.Core.Services.DataService.<GetWorkDays>d__c.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at TimeStamp.Core.ViewModel.MainViewModel.<LoadWorkDays>d__1a.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.AsyncMethodBuilderCore.<ThrowAsync>b__3(Object state)}

Edit 2:

I played around a bit more and figured out the following.

var query = Connection.Table<WorkDay>().Where(wd => wd.Date >= @from && wd.Date <= to);
return query.ToListAsync().ConfigureAwait(false);

When executing this statement it actually breaks in the ToListAsync() method instead of the Where method. However, this doesn't help either.

Later I tried the following which actually works.

var result = await Connection.Table<WorkDay>().ToListAsync().ConfigureAwait(false);
return result.Where(wd => wd.Date >= @from && wd.Date <= to).ToList();

So what I did is to separate the Where method actually. But although this works for me it does not answer my question because I'm still wondering why this does not work.

Sweetheart

What do you mean by "NRE in the where clause"? If it's actually compiling into an expression tree and being processed as such, it shouldn't be possible for the lambda itself to throw an exception, only the code that processes it. Do you actually get an exception before "ToListAsync" is ever called, or did you just mean "if I remove the Where, there's no exception" by that? – Schist

Are your from and to variables defined? I also see to here does not have an @. and do you have null Dates in WorkDay? – Caudex

@Caudex Yes. I checked using the debugger that both, from and to have a value. from has the @ because it is also a keyword in C# and therefore it is usually not allowed to use a variable with that name. – Sweetheart

@MattiVirkkunen Exactly. When I remove the Where() method then everything works fine. I also added the stack trace now. You can see that it fails in CompileExpr which I tend to believe refers to the Where expression. Maybe there is a better way to look into this error? – Sweetheart

@Stephan: It seems there's only a couple of source files involved in this library, so if you temporarily copied those into your project instead of using a DLL you could debug within CompileExpr and see what exactly is breaking. – Schist

What about null dates in your table? – Caudex

@Caudex Nope. Checked that by downloading the database from the Windows Phone emulator by using the ISETool and used SQLite Administrator to have a look at the table. All entries (just some test entries) all have a date assigned. No null values there. – Sweetheart

What happens if your clause looks like this: .Where(wd => wd != null && wd.Date >= @from && wd.Date <= to) ? Just as investigation.. Also, your fix seems suboptimal, as you pull everything from your WorkDay table, then filter it afterwards. – Missie

Answer 1

I'm pretty sure you are not working in this project anymore, but I'm going to answer it because it may help someone which still is struggling with this problem.

The problem is your entity and I don't know what exactly is the problem because you didn't post your class here. But basically you probably were trying to access a property that wasn't fetch from your database yet. For example, I have a class with two properties:

public class MyClass
{
      public DateTime Date { get; set; } // You're saving this property in the database
      public bool IsLate => Date < DateTime.Today; // This property is not be saving, and probably is the cause of your exception
}

I don't know why SQLite does that, but when it is querying your entities, if in your query you're filtering by IsLate property, it's going to crash because Date property wasn't fetch yet.

For solving this you'll have to fetch the entities first, then filter by this property. That's basically what you did in EDIT 2. You've fetch all entities then filtered.

Corrie

I was having this same problem and it was as you suggested. However rather than convert it values to a list then running a query on it I was thinking doing: query.AsEnumerable().Where(....) as IEnumerable is lazily evaluated, thus the entire table doesn't need to be fetched. Is my assumption correct? – Deciliter

I'm not sure if that would solve solve your problem, because SQLite works differently of Entity Framework I believe. The reason you can do this using EF is because DbContext implements IQueryable, but that's not the case using SQLite. – Corrie

Answer 2

Maybe I am too new to know what I am talking about, but I think your last example just needed an await statement before your ToListAsync call.

var query = Connection.Table<WorkDay>().Where(wd => wd.Date >= @from && wd.Date <= to);
return await query.ToListAsync().ConfigureAwait(false);

Not sure on the specifics of the first problem, but I would think that it has something to do with the TableAsyncQuery object, that the Where statement produces, not being fully initialized before the ToListAsync calls it on another thread.

Primordial

Unfortunately, I can't test this anymore as I'm using another database now instead of SQLite (iBoxDB). But I'm pretty sure that Visual Studio would complain in case I would try to access a Task result with out async there. But I may be mistaken. – Sweetheart

Answer 3

adding bit more details to Daniel Cunha answer i have this in my cProduct class

    [PrimaryKey, AutoIncrement]
    public int rowid
    {
        get => _rowid;
        set => _rowid = value;
    }

    [Ignore]
    public int ID => rowid; // just for convinience

this query works product.Product = dbConnection.Table<cProduct>().Where(s => s.rowid == product.ProductID).FirstOrDefault();

but this fails with null reference exception

product.Product = dbConnection.Table<cProduct>().Where(s => s.ID == product.ProductID).FirstOrDefault();

s.ID can't be use in db query.. there is not such field in the table.

Deedeeann