CODEWITHSUNDEEP
sql-serverdapper
webworm
webworm

Reputation: 11019

Checking if record exists using Dapper ORM

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

Upvotes: 0

Views: 4895

Answers (2)

pim
pim

Reputation: 12587

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();

Upvotes: 0

Mrinal Kamboj
Mrinal Kamboj

Reputation: 11478

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

Upvotes: 2

Related Questions