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.

I dont understand your question. Why do you have `return`s? There is nothing after them so what purpose do they serve? — CodingYoshi, Jan 31 '18 at 23:22
Oops, forgot to edit them out, they were there to return the teststring, edited. — Kael Tharas, Jan 31 '18 at 23:23
My question is whether there is an option to actually save the ObjectID (not the status which it asks for) in the list as the IDs were somewhat like 14325617892 saved as long; so far all examples I've seen used the select to point to the specific element inside the objects which fit the conditions, it does only save 0 instead of the actual value as long which I can't figure out why it does so. — Kael Tharas, Jan 31 '18 at 23:28
ATM the problem with this is that i later get the object, change the value and save it basing on this ID which I wasn't able to extract with this method anyhow. — Kael Tharas, Jan 31 '18 at 23:30
You are getting records whose status is 0 or 1, then you are setting it to 5. So does that work? Is that the question? — CodingYoshi, Jan 31 '18 at 23:34
The records which I get should be the ID of the entries, not the status which, if either is 0 or 1, should be enough to mark that row as selected. What i then try to extract to the list is this ID as long. When all the IDs where the status in the same object is either 0 or 1 I want to update the status of those objects to 5 in the foreach loop,so I fetch the row where that ID was from to a temporary object, set the status to 5 and call the UpdateAsync(object) part of SaveObjectAsync to update the status(Index!=0). — Kael Tharas, Jan 31 '18 at 23:42
I get that. So what was the result? Does the status get updated? What is the problem? — CodingYoshi, Jan 31 '18 at 23:46
The problem is that the first list doesn't get filled with the ObjectID but only with a 0 for all entries that match the query, therefore the foreach loop also only passes the ID part as 0 where I try to fetch the object through this ID and since none of those IDs is 0 the result of that action, due to FirstOrDefaultAsync, returns null and doesn't go into the saveloop (otherwise it would give an exception and stop the debugger). Since the list is filled with 0's it's clear to me that those further steps basically do nothing, so my problem is still the first query. — Kael Tharas, Jan 31 '18 at 23:51
The foreach should execute the query. You can force the execution by calling `ToList` if you want. Try this and see if the result is différent: `var ids = ObjectIDList.ToList();`. Then loop through `ids`. — CodingYoshi, Jan 31 '18 at 23:58
Using var ObjectIDList = await database.QueryAsync("SELECT ObjectID FROM Object WHERE ObjectStatus = 0 OR ObjectStatus = 1"); var ids = ObjectIDList.ToList(); still only returns only a 0 and therefore still doesn't update the status of the objects that should be corresponding and therefore doesn't do anything to the actual values in the database. Is it possible that the query isn't able to convert the objects to a list of longs? In that case is it necessary to actually define a new type which only includes a long this query should return the value to? — Kael Tharas, Feb 01 '18 at 00:08
Thats really weird. Lets try something else. You have a custom type named `Object`. How about: `QueryAsync — CodingYoshi, Feb 01 '18 at 00:16

score 1 · Accepted Answer · answered Feb 01 '18 at 00:47

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

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

1 Answers1