koobas.hobune.stream
Using Linq with SQLite
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!!!
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?
Thanks WeirdBeardDev. I was thinking it might be something along those lines. Its actually a part of the System.Data.Linq.$$anonymous$$apping namespace. So being a part of Linq, I would have assumed it would support IEnumerable All the examples read used this to ... so really stumped.
I found Table<T>under the System.Data.Linq namespace and it does support IEnumerable<T>. After rereading your code I don't think has to do with the loop. Have you tried separating out the print statement into two lines. Then you could see if it's one or both of those lines. Another option is to see if the id and seats values are null.
The error pops up on the line for the first line of the 'for' loop, so doesn't even make it to the print statement.
Got what I needed to do done anyway, but just curious was this isn't working when I have seen it in a couple of examples.
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 :)
