If you enabled logging (as I’ve requested in my initial response)
LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);
It will tell you what the exception was:
DEBUG: SQL: SELECT "UV_Child"."KindergartenId", "UV_Child"."KindergartenName", "UV_Child"."ClassId", "UV_Child"."ClassName", "UV_Child"."ChildId", "UV_Child"."ChildName", "UV_Child"."ParentId", "UV_Child"."DisplayName", "UV_Child"."UserName", "UV_Child"."PhoneNumber", "UV_Child"."ImageName"
FROM "UV_Child"
WHERE "UV_Child"."ClassId" = 112
LIMIT 100
ERROR: ServiceBase<TRequest>::Service Exception, Exception: SQLite error
no such column: UV_Child.PhoneNumber
This is saying it can’t find PhoneNumber column. Which is strange because UV_Child does have a PhoneNumber column:
[Alias("UV_Child")]
public partial class UV_Child
{
public int? KindergartenId { get; set;}
public string KindergartenName { get; set;}
public int? ClassId { get; set;}
public string ClassName { get; set;}
[Required]
public int ChildId { get; set;}
[Required]
public string ChildName { get; set;}
public int? ParentId { get; set;}
public string DisplayName { get; set;}
public string UserName { get; set;}
public string PhoneNumber { get; set;}
public string ImageName { get; set;}
}
However I noticed you’re actually not using an in-memory Sqlite database, you’re instead using a pre-existing Sqlite database:
var dbPath = "~/../dbSqlite.db".MapHostAbsolutePath();
container.Register<IDbConnectionFactory>(
c => new OrmLiteConnectionFactory(dbPath, SqliteDialect.Provider));
Which means I can’t verify that the schema in this database actually matches the model you’re trying to query, which also shows this error when trying to query the table with Raw SQL that AutoQuery generated:
var sql = @"SELECT ""UV_Child"".""KindergartenId"",
""UV_Child"".""KindergartenName"",
""UV_Child"".""ClassId"",
""UV_Child"".""ClassName"",
""UV_Child"".""ChildId"",
""UV_Child"".""ChildName"",
""UV_Child"".""ParentId"",
""UV_Child"".""DisplayName"",
""UV_Child"".""UserName"",
""UV_Child"".""PhoneNumber"",
""UV_Child"".""ImageName""
FROM ""UV_Child""
WHERE ""UV_Child"".""ClassId"" = 112
LIMIT 100";
using (var db = HostContext.Resolve<IDbConnectionFactory>().Open())
{
var results = db.Select<UV_Child>(sql);
results.PrintDump();
}
AutoQuery generates the correct SQL here and after removing the reference to "UV_Child"."PhoneNumber" the query works!
To make sure it’s not an issue with OrmLite I’ve created a new test project using a new Sqlite database that’s created with a new table which I can verify the schema is created with the table.
[TestFixture]
public class StandAloneTests
{
private IDbConnectionFactory dbFactory;
public StandAloneTests()
{
this.dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider);
}
[Test]
public void Query_children_by_teacher_test()
{
var sql = @"SELECT ""UV_Child"".""KindergartenId"",
""UV_Child"".""KindergartenName"",
""UV_Child"".""ClassId"",
""UV_Child"".""ClassName"",
""UV_Child"".""ChildId"",
""UV_Child"".""ChildName"",
""UV_Child"".""ParentId"",
""UV_Child"".""DisplayName"",
""UV_Child"".""UserName"",
""UV_Child"".""PhoneNumber"",
""UV_Child"".""ImageName""
FROM ""UV_Child""
WHERE ""UV_Child"".""ClassId"" = 112
LIMIT 100";
using (var db = dbFactory.Open())
{
db.DropAndCreateTable<UV_Child>();
var results = db.Select<UV_Child>(sql);
results.PrintDump();
}
}
}
And this works as well! So after spending hours trying to track down this issue it doesn’t appear to be an issue with AutoQuery or OrmLite. I don’t know what the issue is with your Sqlite database, but I suspect your database may be corrupted.
I’ve also no idea how you managed to even build your solution with classes that containing illegal characters like this:
[Alias("UV_Stats_PageReportMaster")]
public partial class UV_Stats_PageReportMaster
{
[AutoIncrement]
public int MasterId { get; set;}
public int? PageReportType { get; set;}
public string ±»·ÃÎʹ¦ÄÜ { get; set;}
public int? ·ÃÎÊ×ÜÊý { get; set;}
}
But to save me wasted hours in future, I’m going to require any future issues to include an isolated, stand-alone repro of the issue. Not your entire solution, not an existing database - an isolated repro that compiles, that I can run, that only has the minimal code to demonstrate that it is an issue with the ServiceStack libraries and not any external state or environment issues.
Unrelated to this issue please also read the OrmLite limitations where every Table needs a single Primary Key, i.e. UV_Child doesn’t have one, you can add one with the [PrimaryKey] attribute.
