Select Count(*) Query using Dapper in .Net Core API returns incorrect value

Question

I'm trying to do a select count query in Sql Server using Dapper. The expected response should be 0 when a profile does not exist. When I do the query in SSMS it returns correctly, but in the API using Dapper it returns 1. Any idea why this is happening?

public IActionResult GetProfileCount(string profileId)
    {
        int profileCount = 0;
        using (IDbConnection db = new SqlConnection(connectionString))
        {
            try
            {
                profileCount = db.Query($"select count(*) from Profile where Id='{profileId}'").Count();
            }
            catch(Exception ex)
            {
                Console.WriteLine($"Error retrieving count for ProfileId: {profileId}", ex.Message);
            }
        }

        return Ok(profileCount);
    }

Answer 1

Another option is use the method ExecuteScalar for "select count" queries:

profileCount = db.ExecuteScalar<int>("select count(*) from Profile where Id=@profileId", new { profileId });

Ref.: https://www.learndapper.com/selecting-scalar-values

Answer 2

Try and change your query to the following:

db.Query($"select count(*) from Profile where Id = @ProfileId", new { ProfileId = profileId }).Count()

Answer 3

I figured it out. The .Count() is counting the rows of the result, which is going to be 1 because the result is one row displaying the number 0. I switched my code to this and it works now.

public IActionResult GetProfileCount(string profileId)
{
    int profileCount = 0;
    using (IDbConnection db = new SqlConnection(connectionString))
    {
        try
        {
            profileCount = db.Query($"select * from Profile where Id='{profileId}'").Count();
        }
        catch(Exception ex)
        {
            Console.WriteLine($"Error retrieving count for ProfileId: {profileId}", ex.Message);
        }
    }

    return Ok(profileCount);
}