SQLite.Net-PCL Strange select behaviour

Question

Using any of the NuGet packages: SQLite.Net-PCL - Win32 Platform, SQLite.Net-PCL - XamarinIOS Platform or SQLite.Net-PCL XamarinAndroid Platform I'm having problems with select in general. Specifically whenever I select from the database in either LINQ or raw SQL I'm getting back what seems to be an object containing default values.

Here is an example console application to demonstrate my problem:

using System;
using System.Linq;
using SQLite.Net;
using SQLite.Net.Platform.Win32;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            // Open a connection to the database
            using (var database = new SQLiteConnection(new SQLitePlatformWin32(), "db.db"))
            {
                // Create a simple table
                database.CreateTable<Entity>();

                // Add a simple record to it each time we start the application
                database.Insert(new Entity { Data = Guid.NewGuid().ToString(), BoolData = true, IntData = 5 });

                Console.WriteLine("---------> Inserted item:");

                // Display all our records
                foreach (var e in database.Table<Entity>())
                {
                    Console.WriteLine(e);
                }

                Console.WriteLine(Environment.NewLine);
                Console.WriteLine("---------> Linq select Ids:");

                // For every record we'll select the Id field - this is not working
                foreach (var e in database.Table<Entity>().Select(e => e.Id))
                {
                    Console.WriteLine(e);
                }

                Console.WriteLine(Environment.NewLine);
                Console.WriteLine("---------> Id by scalar query:");

                // Let's try going after a value explicitly - this is fine
                var r1 = database.ExecuteScalar<int>("SELECT Id FROM Entity WHERE Id == 1");

                Console.WriteLine(r1);
                Console.WriteLine(Environment.NewLine);
                Console.WriteLine("---------> Ids by query:");

                // So lets try going after our Id field from a query - this still dosen't work
                foreach (var e in database.Query<int>("SELECT Id FROM Entity"))
                {
                    Console.WriteLine(e);
                }

                Console.WriteLine(Environment.NewLine);
                Console.WriteLine("---------> Linq select Ids after force to memory:");

                // Now lets try forcing a where to execute before performing the select - this works but it's bad
                foreach (var e in database.Table<Entity>().Where(e => e.IntData == 5).ToList().Select(e => e.Id))
                {
                    Console.WriteLine(e);
                }

                Console.ReadKey();
            }
        }
    }
}

Entity is just a simple POD:

using SQLite.Net.Attributes;

namespace ConsoleApplication1
{
    public class Entity
    {
        public Entity() { }

        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        public string Data { get; set; }

        public int IntData { get; set; }

        public bool BoolData { get; set; }

        public override string ToString()
        {
            return string.Format("Id: {0}, Data: {1}, IntData: {2}, BoolData: {3}", Id, Data, IntData, BoolData);
        }
    }
}

So the only way I was able to get it to work was to force a where into memory first... this is bad in the case where we may have data that contains BLOBs and so forth which we are not interested in.

Is this behaviour intentional or did I miss something?

Answer 1

I resolved this by using a POD class containing the discrete fields that I was querying for via the SQLIteConnection.Query("Some SQL query") method. Where T would be the specific POD containing the fields of interest. As long as the field names matched the fields listed after SELECT it was able to map them.

I still think this is still less than ideal because we don't want to be creating a bunch of classes to represent each query result. There might be something we can do with Anonymous Types but that would likely be messy and expensive at runtime.