Object must implement IConvertible Exception, when using dapper with .net core

Question

I am using asp net core in my project with dapper ORM and Postgresql as database. I want to check if a user with a particular UUID (GUID in C#) value exists in the database or not. Following is my code to check the same:

public async Task<bool> DoesRecordExistAsync(Guid columnValue)
{

    bool doesRecordExist;

    doesRecordExist = await _connection.ExecuteScalarAsync<bool>("SELECT * FROM employee_master WHERE employee_id = @columnValue;", new { columnValue });

    return doesRecordExist;
}

Note: _connection is IDbconnection instance.

When executing the statement, I am getting the below error:

Object must implement IConvertible.

Is there anything wrong with the above code.

Answer 1

Your query "SELECT * FROM ...." is returning matching rows from database; NOT the bool that you are expecting. The ExecuteScalar will return first column of first row. One cannot guarantee that that column is always a Boolean; and basically, you are not expecting value of that column either.

The query should be something like select exists(select 1 from... as explained here.
It will return true/false which you can then handle with await _connection.ExecuteScalarAsync<bool>(.......
So, the new code becomes:

bool exists = await _connection.ExecuteScalarAsync<bool>("select exists(select 1 from....");
return exists;

Alternatively (slow; not recommended), you should change your query to something SELECT COUNT(*) FROM ... to get the count of records. Read more about optimization here.
Then you can cast it to bool something like below:

int count = await _connection.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM..........");
bool exists = count == 0 ? false : true;
return exists;