How to check for null values with Dapper

2
votes

I'm having an SQL table Countries, with CountryId and StateId. StateId can be null, as not all countries have States. I'd like to check if the combination of country/state exists in the table. If I have the country "NL" and state NULL in the table, and I use the following query where countryId = "NL" and stateId = null:

return await conn.ExecuteScalarAsync<bool>(@"SELECT COUNT(*)
                                             FROM [dbo].[Countries]
                                             WHERE [CountryId] = @CountryId
                                             AND [StateId] = @StateId",
                                             new { countryId, stateId });

It will return false. I expected a true response. Could someone explain this behaviour and what is the best approach to solve this?

2
c#.netdapper
That is how SQL works. It is nothing to do with Dapper, or C# or .NET. If you do [StateId] = NULL then you won't get any results - check it for yourself in SSMS (or your SQL client of choice). You need to include AND [StateId] = @StateId only if stateId is not null (or check the answer below if you are explicitly interested in it being null). - mjwills

2 Answers

4
votes

That is not a Dapper issue. Comparing anything to NULL with the = operator will usually return false. You will have to use "is null" for comparison, ie

AND [StateId] is null

(at least on SQL Server). That means of course that your query will have to look differently in case stateid is null.

0
votes

Check this:

SELECT COUNT(*)
       FROM [dbo].[Countries]
       WHERE [CountryId] = @CountryId
       AND ([StateId] IS NULL OR ([StateId] IS NOT NULL AND [StateId] = @StateId))

It will check if StateId is null, then only the 'CountryId' will be checked for those countries with no state, if StateId is not null, then StateId would be checked too