Checking if record exists using Dapper ORM

Question

Checking if record exists using Dapper ORM

dapper sql-server

Question

I am new to Dapper, so I may be missing something obvious but I don't understand why this query is returning null even though the record exists in the table.

queryResult = db.Query<dynamic>(@"SELECT Id FROM Customer WHERE CustomerId = @CustomerId", 
                new { CustomerId = theCustomerId }).FirstOrDefault();

I am checking to see if the record exists and in this case it does, yet queryResult is always null. The @CustomerId parameter is a string that I am matching exactly..

If I run the SQL in SQL Server is brings up the record no problem ...

SELECT Id FROM Customer WHERE CustomerId = 'abc123'

where abc123 is the CustomerId

webworm

Popular Answer

Best way to check for existence using dapper would be:

string sql = "SELECT count(1) FROM Customer WHERE CustomerId = @CustomerId;";

bool exists = false;

using (var connection = new SqlConnection("connection string"))
{
    connection.Open();
    exists = connection.ExecuteScalar<bool>(sql, new { CustomerId = "abc123" });
}

As to why your specific example returns null, I suspect it's because you needed brackets around the db.Query like:

queryResult = (db.Query<dynamic>(@"SELECT Id FROM Customer WHERE CustomerId = @CustomerId", 
            new { CustomerId = theCustomerId })).FirstOrDefault();

pimbrouwers

View more on Stack Overflow

Mrinal Kamboj · Accepted Answer · 8/18/2016 7:54:13 AM

It returns null as you want it to do so

Following is your query getting executed, as part of Query API

"SELECT Id FROM Customer WHERE CustomerId = @CustomerId", 
                new { CustomerId = theCustomerId }

Now what happens when CustomerId doesn't match, it produces empty IEnumerable<dynamic>, though its a different story why dynamic, you shall use integer assuming Id is an integer

but what FirstOrDefault() does on finding an empty IEnumerable, returns null, so simply remove it do check like Any, dapper by default doesn't return null, your code is forcing it

Checking if record exists using Dapper ORM

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator