SimpleRepository Boolean mapping problem

Question

I am using SubSonic (v3.0.0.3) to do simple object mapping with SimpleRepository to SQLite. Everything works fine with Properties of type string, DateTime, Guid and int. When I try to add a bool (or Boolean) Property to the object, I can add it to the database just fine, but when I try to retrieve it, I get the following error message:

"Object of type 'System.Byte' cannot be converted to type 'System.Boolean'."

I've tried retrieving the data a few different ways. Here are some examples (which work when the object in question does not have any bool Properties):

var myObjs = repo.All<MyObj>();

OR

var myObjs = from m in repo.All<MyObj>()
    orderby m.Title
    select m;

I am not sure if this is a bug, or if I am doing something wrong. Is anyone else able to map this data type to SQLite using SimpleRepository?

Thanks!
C

Answer 1

Fixed this for myself, not sure if it is a bug in subsonic but dug into the source to find a fix.

Grab the subsonic source from github and open up

SubSonic-3.0\SubSonic.Core\SQLGeneration\Schema\SQLiteSchema.cs

   public override string GetNativeType(DbType dbType)
    {
        switch(dbType)
        {
            case DbType.Object:
            case DbType.AnsiString:
            case DbType.AnsiStringFixedLength:
            case DbType.String:
            case DbType.StringFixedLength:
                return "nvarchar";
            case DbType.Boolean:
                return "boolean"; // <-- This was set to "tinyint"
            case DbType.SByte:
            case DbType.Binary:
            case DbType.Byte:
                return "longblob";
            case DbType.Currency:
                return "money";

...

Notice where I have commented, Subsonic now maps the .net DbType to the SqlLite type "boolean" instead of "tinyint". Rebuild in release mode and grab your new SubSonic.Core.dll and substitute it.

If you're not familiar with git I can add on some instructions on a quick clone through cygwin. If you havn't got the time for that I can give you the dll I'm using now, however it's a simple change and it's always best to change it yourself rather than taking a compiled dll from an unknown ;)

Answer 2

Hi, I also had this problem & found the issue.

Its in the SQLite.ttinclude in the GetDbType() function, there is no boolean defined.

just add this into the switch

case "boolean": return DbType.Boolean;

and it should work again ;D

Answer 3

Here is a bug report for MySQL: MySQL TINYINT(1) boolean conversion fails and relevant commit: fixed situation where TreatTinyAsBoolean had no effect with default database character set to UTF8.