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