Using Linq with SQLite

Questions & Answers
1

I have been able to setup SQLite, add data, query etc with SQL expressions but I am now trying to convert these over to Linq. Unfortunately its not been very easy as references seem to be limited for Unity and I have just been running of plain c# examples/documentation.

I have set up a simple class:

using Mono.Data.Sqlite;
using System.Data;
using System.Linq;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using UnityEngine;


[Table(Name = "company")]
class Company
{
    [Column(Name = "id")]
    public int id { get; set; }

    [Column(Name = "seats")]
    public int seats { get; set; }
}

And a simple test method:

public void QueryTable()
{
    using (var connection = new SqliteConnection(_dbPath))
    {
        DataContext context = new DataContext(connection);
        Table<Company> companies = context.GetTable<Company>();

        var query =
            from c in companies
            where c.id == 1
            select c;

        foreach (var c in query)
        {
            print(c.id);
            print(c.seats);
        }

        foreach (var company in companies)
        {
            var text = string.Format("Company: {0} {1}",
                company.id.ToString(), company.seats.ToString());
            Debug.Log(text);
        }
    }
}

The first part works with var query and the foreach loop after. But the second foreach loop (which I have seen plenty of examples) is throwing the following error:

FormatException: Input string was not in a correct format.
System.Data.Linq.Table`1[TEntity].GetEnumerator () (at :0)
TestDBLinq.QueryTable () (at Assets/Scripts/TestDBLinq.cs:68) < — this refers to the first line of the second foreach loop.

I been looking for a few hours now and cant find an example of this error.

Thanks in advance!!!

3

I can’t find a reference to the Table<T> class you’re using, I assume it’s part of the Mono.Data.Sqlite namespace. I suspect it does not support IEnumerator<T> or IEnumerable<T>. In your first foreach loop query represents an IEnumerable<T> so it can be iterated over. In the second foreach loop you’re trying to iterate over the Table<T> and unless it supports IEnumerable<T> it won’t work. Can you provide a link to the API doc?

2

Will just add that I just added try/catch and the exception returned:
Except for query is: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber (System.String str, System.Globalization.NumberStyles options, System.Number+NumberBuffer& number, System.Globalization.NumberFormatInfo info

So whats returning is conflicting with the initial class table I created? If so, why is the first query working fine and not conflicting?

Thanks again :slight_smile: