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

user_11660683 0
Viewed 7067

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);
    }
dapper asp.net-core
4 Answers

I see you added your own answer but could I recommend not doing it that way. When you do

profileCount = db.Query($"select * from Profile where Id='{profileId}'").Count();

What you are actually doing is selecting every field from the database, pulling it into your C# application, and then counting how many results you got back. Then you are binning all that data you got back, very inefficient!

Change it to this :

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

Instead you are selecting an "int" from the result set, which just so happens to be your count(*). Perfect!

More on querying in Dapper here : https://dotnetcoretutorials.com/2019/08/05/dapper-in-net-core-part-2-dapper-query-basics/

Also notice that (similar to the other answer), I am using parameterized queries. I also heavily recommend this as it protects you from SQL Injection. Your initial example is very vulnerable!

You can read a little more about SQL Injection in C#/MSSQL here https://dotnetcoretutorials.com/2017/10/11/owasp-top-10-asp-net-core-sql-injection/ But just know that Dapper protects you from it as long as you use the inbuilt helpers to add parameters to your queries.

user_1775160
user_64367410

Try and change your query to the following:

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

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);
}
user_116606830
Related