SqLite ReadFirstAsync<int>

[Olbrasoft]

public override async Task<IResultWithTotalCount> HandleAsync(PagedAnsweredQuestionsQuery query, CancellationToken token)
{
//const string sql = @"SELECT Questions.Id, Questions.DateCreated, Questions.DateAnswered, Questions.QuestionText, Questions.DisplayName, Questions.EmailAddress, Questions.AnswerText, Categories.Name AS CategoryName
//FROM Questions INNER JOIN
//Categories ON Questions.CategoryId = Categories.Id
//WHERE(NOT(Questions.DateAnswered IS NULL))
//ORDER BY Questions.DateAnswered DESC LIMIT @take OFFSET @Skip;

        //SELECT count(id) FROM Questions WHERE (NOT (Questions.DateAnswered IS NULL))";


        const string sql = @"SELECT Questions.Id, Questions.DateCreated, Questions.DateAnswered, Questions.QuestionText, Questions.DisplayName, Questions.EmailAddress, Questions.AnswerText, Categories.Name AS CategoryName
        FROM Questions INNER JOIN
        Categories ON Questions.CategoryId = Categories.Id
        WHERE(NOT(Questions.DateAnswered IS NULL))
        ORDER BY Questions.DateAnswered DESC OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

        SELECT count(id) FROM Questions WHERE (NOT (Questions.DateAnswered IS NULL))";


        IResultWithTotalCount<QuestionDto> result;

        using (var connection = GetConnection())
        {
            using (var multi = connection.QueryMultipleAsync(sql, new { Take = query.Paging.PageSize, Skip = query.Paging.CalculateSkip() }).Result)
            {
                result = new ResultWithTotalCount<QuestionDto>
                {
                    Result = (await multi.ReadAsync<QuestionDto>()).ToArray(),


                    // SQLite TotalCount = (int) await multi.ReadFirstAsync<long>()

                    TotalCount = await multi.ReadFirstAsync<int>()
                };
            }
        }
        
        return result;
    }

[mgravell]

Can you add some words here? I'm not sure if you're asking a question, reporting an issue, or just... showing me some code. Please clarify.

[Olbrasoft]

For MSSql Server works ReadFirstAsync<int> but SQLite ReadFirstAsync<int> error convert int64 to int32 , it can be bypassed (int) await multi.ReadFirstAsync<long>()
(https://github.com/Olbrasoft/AskMe/blob/master/src/Olbrasoft.AskMe.Data.Dapper/QueryHandlers/PagedAnsweredQuestionsQueryHandler.cs)

I'm not good in English

[PromontoryProtean]

I am running into a similar problem. This works:

var rowCount = await dbConnection.ExecuteScalarAsync<int>("SELECT COUNT(Id) FROM TheTable");
var someRows = await dbConnection.QueryAsync<MyRecordClass>("SELECT Id,SomeColumn FROM TheTable");

But if I try to do the same thing using QueryMultiple to avoid the second round trip, I get the following error on the rowCount query:
Unable to cast object of type 'System.Int64' to type 'System.Int32'

dbc.QueryMultiple code that fails:

var sql = "SELECT COUNT(Id) FROM TheTable;SELECT Id,SomeColumn FROM TheTable";
using (var multi = dbConnection.QueryMultiple(sql))
{
   var rowCount = await multi.ReadSingleAsync<int>();
   var someRows = await multi.ReadAsync<AdminNode>();
}

I remember running into a similar issue years ago using one of the Query extension method variations with SQLite but it was resolved at some point.

I am able to work around the bug by doing:

using (var multi = dbConnection.QueryMultiple(sql))
{
   var rowCount = (int)await multi.ReadSingleAsync<long>();
   var someRows = await multi.ReadAsync<AdminNode>();
}

But this creates additional problems when working with pluggable db providers.

For context, I am running into this bug using .NET Core 3 preview 9, Dapper 2.04 and System.Data.SQLite.Core 1.0.111

[PromontoryProtean]

@mgravell I have been doing some digging through old issues, and this problem looks related to
#524

So I did some more testing. The following gives Int64 as output:

var count = dbc.QuerySingle($"SELECT COUNT(Id) AS RecordCount FROM TheTable");
Console.WriteLine(count.GetType().Name);

So it seems the SQLite provider is sending back a long for whatever reason. This is on a type in SQLite that is set to "integer". But I think I read somewhere at some point that SQLite returns identity columns as Int64.

But the following succeeds and somewhere along the line a conversion is happening successfully:
int count = dbc.QuerySingle<int>($"SELECT COUNT(Id) AS RecordCount FROM TheTable");

Continuing on to the grid reader, the following outputs Int64:

var count = multi.ReadSingle($"SELECT COUNT(Id) AS RecordCount FROM TheTable");
Console.WriteLine(count.GetType().Name);

But the following throws "Unable to cast object of type 'System.Int64' to type 'System.Int32'"
int count = multi.ReadSingle<int>($"SELECT COUNT(Id) AS RecordCount FROM TheTable");

Which leads me to the roundabout conclusion that this is related to 524

Apologies if I repeated myself a bit here. Just trying to explain as clearly as I can.

[NickCraver]

This is the same issue as #1496 - resolving in #1663.