servicestack null ref error when using native SQL and ORMLite. Dapper error

Question

I am getting an error trying to get this data with ORMLite. I am pretty sure its failing because the ParentID is null. But I don't know how to fix it.

It errors when I call this method.

 return Db.Query<GetCompaniesById>("select * FROM [Company];");

It works fine if I call it with

var q = Db.From<Company>(Db.TableAlias("c1"))
            .Join<Company>((ChildComp, ParentCompany) =>
                ChildComp.Id == ParentCompany.ParentId
                && ParentCompany.Id == request.Id, Db.TableAlias("c2")).Select<Company>(p => new {Id = Sql.TableAlias(p.Id, "c2"), Name = Sql.TableAlias(p.Name, "c2")});

The error

+       $exception  {System.Data.DataException: Error parsing column 3 (ParentId=1 - Int64) ---> System.InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.Nullable`1[System.Int32]'.
   at Deserializea4b39d89-32a6-4a82-89cf-2e520c205673(IDataReader )
   --- End of inner exception stack trace ---
   at ServiceStack.OrmLite.Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value)
   at Deserializea4b39d89-32a6-4a82-89cf-2e520c205673(IDataReader )
   at ServiceStack.OrmLite.Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext()
   at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ServiceStack.OrmLite.Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
   at cbw.service.interfaces.Services.CompanyService.Get(GetCompaniesById request)
   at ServiceStack.Host.ServiceRunner`1.ExecuteAsync(IRequest req, Object instance, TRequest requestDto)}   System.Data.DataException


 public class Company : DTOServiceStackBase
    {
        [AutoIncrement]
        [PrimaryKey]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public string Address { get; set; }

        public int? ParentId { get; set; }

        [IgnoreDataMember]
        public List<Company> SubCompanies { get; set; }
    }

[Route("/Company/{Id}", "GET")]
    public class GetCompaniesById : IReturn<GetCompaniesById>
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public int? ParentId { get; set; }
        public string NotVisible => "Id,ParentId";
    }

 private void CompanyInit()
        {
            int val = 0;
            using (var db = Db)
            {
                db.CreateTable<Company>();
                db.Insert(new Company { Name = "Top Company A" + val++});//1 - Top Company Don't Add Parent ID
                db.Insert(new Company { Name = "Company B" + val++, ParentId = 1 });//2
                db.Insert(new Company { Name = "Company C" + val++, ParentId = 2 });//3
                db.Insert(new Company { Name = "Company D" + val++, ParentId = 3 });//4
                db.Insert(new Company { Name = "Company E" + val++, ParentId = 1 });//5
                db.Insert(new Company { Name = "Company F" + val++, ParentId = 1 });//6
                db.Insert(new Company { Name = "Company G" + val++, ParentId = 1 });//7
                db.Insert(new Company { Name = "Company H" + val++, ParentId = 1 });//8
                db.Insert(new Company { Name = "Company I" + val++, ParentId = 17 });//9

                db.Insert(new Company { Name = "Company J" + val++, ParentId = 4 });//10
                db.Insert(new Company { Name = "Company K" + val++, ParentId = 10 });//11
                db.Insert(new Company { Name = "Company L" + val++, ParentId = 11 });//12
                db.Insert(new Company { Name = "Company M" + val++, ParentId = 2 });//13
                db.Insert(new Company { Name = "Company N" + val++, ParentId = 13 });//14
                db.Insert(new Company { Name = "Company O" + val++, ParentId = 14 });//15
                db.Insert(new Company { Name = "Company P" + val++, ParentId = 15 });//16
                db.Insert(new Company { Name = "Company Q" + val++, ParentId = 16 });//17
                db.Insert(new Company { Name = "Company R" + val++, ParentId = 17 });//18

                //validation it works
                var result = db.SingleById<Company>(1);
                result.PrintDump(); //= {Id: 1, Name:Seed Data}
            }
        }

Answer 1

Have you tried just using OrmLite's SqlList to execute custom SQL?

var results = Db.SqlList<GetCompaniesById>("select * FROM [Company];");

Or a typed version with:

var results = Db.Select<GetCompaniesById>(Db.From<Company>());

With specific fields:

var results = Db.Select<GetCompaniesById>(Db.From<Company>()
    .Select(x => new { x.Id, X.Name, X.Address, x.ParentId }));

An alternative option is to use your existing Company data model then use the Auto Mapping Utils to map it to your preferred type:

 var results = Db.Select<Company>();
 var dtos = results.Map(x => x.ConvertTo<GetCompaniesById>());