Querying a table only returns null to a list in sqlite-net-pcl

Question

so I've been trying a bit around sqlite-net-pcl and this just doesn't seem to be able to update the status... After trying around a bit with a teststring to somewhere localize the problem it starts with the first list which appears to be filled with 0's only so I've somewhat reverted it back to where it was.

public async Task UpdateStatus()
    {
        var ObjectIDList = await database.QueryAsync<long>("SELECT ObjectID FROM Object WHERE ObjectStatus = 0 OR ObjectStatus = 1");
        if (ObjectIDList != null)
        {
            foreach (long ObjectID in ObjectIDList)
            {
                byte newStatus = 5;
                var result = await database.Table<Object>().Where(i => i.ObjectID == ObjectID).FirstOrDefaultAsync();
                if (result != null)
                {
                    result.Objectstatus = newStatus;
                    await SaveObjectAsync(result);
                }
            }
        }
    }

No matter how many entries there are in my table, whenever there is either a 0 or a 1 in the object's status value it filled the list with another 0.

Question

Why is ObjectIDList always returning zeros even though I have many records in the database whose ObjectStatus = 0 or ObjectStatus = 1? If I have 5 records, then it returns 5 results but with zeros instead of the actual ObjectID.

Answer 1

I looked at the source code, and here is what SQLite does. Let's say you have a query like this:

var ObjectIDList = await database.QueryAsync<long>("SELECT ObjectID FROM 
    Object WHERE ObjectStatus = 0 OR ObjectStatus = 1");

SQLite will executed the query against the database, and create an instance of T you passed into QueryAsync<T>. You passed a long so it will create a new long. Then it will try and populate a property named ObjectID within the instance. But obviously since long does not have a property named ObjectID, it cannot populate it. SQLite does not throw an exception to tell you this. It simply continues and in the end you get a bunch of newly created longs. All the longs by default are set to zero. This is why you are getting all zeros in ObjectIDList.

Look at the source code starting on line 119. Personally, I think this is a bad design and they should just return a list of longs in this case.

Fix

So to fix the problem, you either need to create a new class that has ObjectID as a property or use the one you have already: Object (this is not the .net Object type but your own custom type). So your query will become:

var ObjectIDList = await database.QueryAsync<Object>("SELECT ObjectID FROM 
    Object WHERE ObjectStatus = 0 OR ObjectStatus = 1");

Then do this:

List<long> ids = ObjectIDList.Select(x => x.ObjectID).ToList();

Now loop through and do your work:

foreach(long thisObjectId in ids)
{
    // code...
}