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.

Why would you assume that query could be converted to a `bool`? I assume you would want to do a `SELECT COUNT(*)...` instead and convert that to an `int`. Then return `true` if the int value is greater than zero. — DavidG, Nov 26 '19 at 12:15
ExecuteScalarAsync() will select a single value. If the converted type is specified as bool, it will return bool value depending on whether such a record exists or not. Same as Any() in LINQ. — Sunny, Nov 26 '19 at 12:25
How do you expect Dapper to convert an entire record to a `bool` though? — DavidG, Nov 26 '19 at 12:26
Nope. `ExecuteScalar` (and its async variant) will select the first column of the first row. This type may vary. Do not use `SELECT *` in general, and definitely not when combined with `ExecuteScalar`. — Jeroen Mostert, Nov 26 '19 at 12:27
@DavidG reference is taken from this answer: https://stackoverflow.com/questions/39006512/check-if-record-exists-with-dapper-orm — Sunny, Nov 26 '19 at 12:36
so what should I use to check if the record exists or not? @JeroenMostert — Sunny, Nov 26 '19 at 12:37
@DavidG yes, it was my bad. But it was working when the database table does not contain UUID. Don't know why — Sunny, Nov 26 '19 at 12:48
Because a UUID cannot be converted into a boolean value. An integer ID can be converted though. — DavidG, Nov 26 '19 at 23:06
@DavidG but I was writing [select *], that means the whole table was converted to bool and when the table contains UUID, it causes the problem. Is there any specific reason that only due to UUID whole conversion was hampered? — Sunny, Nov 27 '19 at 04:51
No, the whole table isn't converted, only the first value of the first row. So if that first value was the ID column, it would use that. — DavidG, Nov 27 '19 at 07:38

score 3 · Accepted Answer · edited Apr 24 '23 at 15:33

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;

what about this: bool exists = await _connection.ExecuteScalarAsync("select count(1) from .. "); ? @AmitJoshi — Sunny, Nov 26 '19 at 13:20
@Sunny: `COUNT(1)` does not count single rows, it counts the number of times the constant `1` occurs in the result set (that is, once per row). It's equivalent to `COUNT(42)` or `COUNT(*)`, although some really naive database engines may choose to implement them differently (for example, not reading actual columns when specifying `COUNT(1)`, even though this isn't required for `COUNT(*)` either). — Jeroen Mostert, Nov 26 '19 at 14:02

score 0 · Answer 2 · answered Nov 26 '19 at 12:36

0

Try to use the next query :

SELECT EXISTS(SELECT 1 FROM employee_master WHERE employee_id = @columnValue)

answered Nov 26 '19 at 12:36

WhoKnows

340
1
12

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

2 Answers2