SQLite Int64 vs Int32 Problem and SubSonic ActiveRecord

Question

I thought this was covered elsewhere but I don't see it now. Anyway, having a problem with a simple v3 query. Using SQLite ADO.NET provider 1.0.65.0. My table structure looks like this:

CREATE TABLE "SamplerData" ("RowId" INT PRIMARY KEY  NOT NULL ,"SampName" VARCHAR(128),"SampPurpose" VARCHAR(2048),"ActiveState" INTEGER NOT NULL  DEFAULT 1 )

My Structs1.cs file has this in it:

        Columns.Add(new DatabaseColumn("RowId", this)
        {
                IsPrimaryKey = true,
                DataType = DbType.Int32,
                IsNullable = false,
                AutoIncrement = false,
                IsForeignKey = false
        });

        Columns.Add(new DatabaseColumn("SampName", this)
        {
                IsPrimaryKey = false,
                DataType = DbType.AnsiString,
                IsNullable = true,
                AutoIncrement = false,
                IsForeignKey = false
        });

        Columns.Add(new DatabaseColumn("SampPurpose", this)
        {
                IsPrimaryKey = false,
                DataType = DbType.AnsiString,
                IsNullable = true,
                AutoIncrement = false,
                IsForeignKey = false
        });

        Columns.Add(new DatabaseColumn("ActiveState", this)
        {
                IsPrimaryKey = false,
                DataType = DbType.Int32,
                IsNullable = false,
                AutoIncrement = false,
                IsForeignKey = false
        });

I have a query in a WPF codebehind that looks like this:

SqlQuery sqlsql = new Select()
  .From( "SamplerData" )
  .Where( "ActiveState" )
  .IsEqualTo( 1 );
List<SamplerDatum> sampAll = sqlsql .ExecuteTypedList<SamplerDatum>();

A breakpoint set to show the value of sqlsql shows this:

{SELECT * FROM `SamplerData` WHERE ActiveState = @0}

Then the code throws with:

{"Object of type 'System.Int64' cannot be converted to type 'System.Int32'."}

A "find" in Visual Studio didn't show me where the Int64 conversion was happening. I understand that SQLite uses Int64 for identity columns, but not why/how SubSonic is handling the conversion when the Structs is making it Int32 anyway.

Help?!

Thanks..

Answer 1

I don't know much about SubSonic, but the ADO.NET client for sqlite uses int64 for all integer columns. Without knowing SubSonic, this is just a guess, but you may want to change the DbType.Int32 columns to DbType.Int64.

Most likely, the query is actually executing fine, but the return values (untyped initially, in ADO.NET fashion) are being unboxed into some SubSonic data structures - structures it thinks should be 32-bit integers, based on your DbType.Int32 statement. You cant unbox a long into an it (i.e. (int)(object)(long)0 will throw an Exception) - so you need to tell SubSonic to expect 64-bit integers, since that's what SQLite is going to give you.

Answer 2

We fixed this with the latest release - the SQLite.tt file didn't correctly translate the integer PK to long (int 64). If you grab the latest bits at Github it should be solved. Make sure you grab from the templates project.