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.
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
var x = connection.Query<int>("SELECT COALESCE(max(val), 0) FROM info").Single();
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.
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.