Null Dapper.net query still returning Null Reference Exception with FirstOrDefault()

Question

I would like to return the max Id from a table using Dapper.net

var x = connection.Query<int>("SELECT max(val) FROM info").FirstOrDefault();

This works - unless no row exists then I get an

Object reference not set to an instance of an object.

Shouldn't the 'OrDefault' mean this returns 0 when no records are present?

How can I return 0 - or some non null value to prevent a crash.

thx

Answer 1

The issue is that you're telling Dapper to expect a sequence of int, but you actually have the possiblity of a null value. So you either need to change the type

var x = connection.Query<int?>("SELECT max(val) FROM info").Single() ?? 0;

Or you need to change the query to handle the null.

var x = connection.Query<int>("SELECT COALESCE(max(val), 0) FROM info").Single();

I'm using Single here because this query should only ever return exactly one row.

You would use FirstOrDefault when you expect a sequence and only want the first item, or if there are no items you want the default value of the item type.

Answer 2

var x = connection.Query<int>("SELECT ISNULL(max(val), 0) FROM info").Single();

You can use ISNULL if you want to select default value if value is null.