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
– DavidG, Commented Nov 26, 2019 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
– Sunny, Commented Nov 26, 2019 at 12:25
How do you expect Dapper to convert an entire record to a bool though? — DavidG
– DavidG, Commented Nov 26, 2019 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
– Jeroen Mostert, Commented Nov 26, 2019 at 12:27
@DavidG reference is taken from this answer: stackoverflow.com/questions/39006512/… — Sunny
– Sunny, Commented Nov 26, 2019 at 12:36

MoRe · Accepted Answer · 2023-04-24 15:33:57Z

3

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;

edited Apr 24, 2023 at 15:33

MoRe

2,4042 gold badges5 silver badges26 bronze badges

answered Nov 26, 2019 at 12:50

Amit Joshi

16.6k23 gold badges88 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sunny Over a year ago

what about this: bool exists = await _connection.ExecuteScalarAsync<bool>("select count(1) from .. "); ? @AmitJoshi

Jeroen Mostert Over a year ago

@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).

WhoKnows · Accepted Answer · 2019-11-26 12:36:22Z

0

Try to use the next query :

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

answered Nov 26, 2019 at 12:36

WhoKnows

3702 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related