I have a simple query that is running a batch operation inside an web api (.net core 3.1) hosted on IIS. The problem is that for some databases (normally the ones with size greather than 10-20GB) the query returns false when the result should be true. The database is SQL Server 2012 in one everonment and 2019 in another. But in both the strange behavior happens. My ORM is Dapper in latest version. The query is this one:
SELECT
CASE Column
WHEN 'S' THEN 1
ELSE 0
END AS Result
FROM
Table
WHERE
Id = @Id
The column is of type CHAR(1). In C# we have the below code:
bool result= Context.Connection.QueryFirstOrDefault<bool>(
sql: QUERY,
param: new { Id = id },
transaction: Context.Transaction
);
I have already checked some simple things, like connection string and if a register with the Id parameter value exists.
Running SQL Profiler in both databases I was able to capture the query that dapper is sending for the database:
exec sp_executesql N'
SELECT
CASE Column
WHEN ''S'' THEN 1
ELSE 0
END AS Result
FROM
Table
WHERE
Id = @Id',N'@Id int',@Id=103051026
Running the same query in SSMS 2019 the result is true as expected but in application in production and development environment the result for some databases is sometimes false. When in a single running the result happens to be false it will be false for all calls of the method inside the batch (for batch here you can understand as an execution of the operation in the Web Api for thousands of registers in Table).
Could someone give me some help with this strange behavior?
After the comment of @Charliface this the exception that I'm receiving:
"ERRO" "System.ArgumentNullException: Value cannot be null. (Parameter 'value')
at Dapper.SqlMapper.ReadChar(Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1878
at Dapper.SqlMapper.<>c__DisplayClass198_0.<GetStructDeserializer>b__0(IDataReader r) in C:\projects\dapper\Dapper\SqlMapper.cs:line 2914
at Dapper.SqlMapper.QueryRowImpl[T](IDbConnection cnn, Row row, CommandDefinition& command, Type effectiveType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1199
at Dapper.SqlMapper.QueryFirstOrDefault[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 763
at ProjectNamespace.Repository.RunSimpleQuerz(Nullable`1 codigoTraco, Int32 implantacaoId) in C:\agents\1\_work\113\s\src\Repositorios\Siac\Repository.cs:line 26
at ProjectNamespace.Service.Business.Method5(Evento evento, Rule regraValida, DateTime dataExecucao) in C:\agents\1\_work\113\s\src\Servicos\Something\Servico\Business.cs:line 278
at ProjectNamespace.Service.Business.Method4(Evento evento, Rule regraValida, Dto dto, RegraInteligenciaTecnologica regraCorrente, Nullable`1 codigoTracoCorrente, Int32 anoCorrente, Int32 semanaCorrente, Int32 quatidadeSemanasPeriodo, List`1 list, DateTime dataExecucao) in C:\agents\1\_work\113\s\src\Servicos\Something\Servico\Business.cs:line 100
at ProjectNamespace.Service.Business.Method3(List`1 listaEventos, DateTime dataExecucao) in C:\agents\1\_work\113\s\src\Servicos\Something\Servico\Business.cs:line 64
at ProjectNamespace.Service.Business.Method2(Int32 implantacaoId, DateTime dataInicio, DateTime dataExecucao) in C:\agents\1\_work\113\s\src\Servicos\Something\Servico\Business.cs:line 35
at ProjectNamespace.Service.Business.Method1(Int32 implantacaoId, DateTime dataInicioOperacao, DateTime dataMemoria, Nullable`1 Avaliacao) in C:\agents\1\_work\113\s\src\Servicos\Something\Servico\GerenciadorServico.cs:line 259" "MOTOR" 26
Both ADO.NET and Dapper are just executing queries as supplied; they don't get to decide what happens, so: I can see two possibilities here: 1) the query is correct, and for some interval of time, the result isn't what you expected it to be, but because the data was not what you expected it to be, or 2) you're accessing Context.Connection (for the same connection) from multiple threads, which can lead to undefined behaviour including getting muddled query results from concurrent operations