10

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

Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197
niico
  • 11,206
  • 23
  • 78
  • 161
  • do yo get error in var x = connection.Query("SELECT max(val) FROM info").FirstOrDefault(); or before using x? – Ubiquitous Developers Oct 25 '16 at 11:53
  • Use ISNULL() with max(val) – Vivek Nuna Oct 25 '16 at 11:53
  • @viveknuna thanks for SQL idea - I would like to understand why this isn't working in c# too. – niico Oct 25 '16 at 11:55
  • 2
    That query should only return one value, thus the "OrDefault" is not the issue. Try changing the type from `int` to `int?`. Or change the query to not return null in the first place. – juharr Oct 25 '16 at 11:55
  • You are trying to cast a `NULL` to an `int`. – Panagiotis Kanavos Oct 25 '16 at 11:55
  • @UbiquitousDevelopers i get the error on the var x line - yes before using it. – niico Oct 25 '16 at 11:56
  • @PanagiotisKanavos Shouldn't the 'ordefault' return 0 if the query is null? – niico Oct 25 '16 at 11:57
  • 1
    @niico The Error is occurring before you even get to the `FirstOrDefault` part when Dapper is trying to put a `null` into an `int`. – juharr Oct 25 '16 at 11:57
  • @juharr That makes sense - how can I make Dapper not return a null reference exception (without changing the sql)? – niico Oct 25 '16 at 12:03
  • The issue here is that `select max(val) from info`, when `info` contains no values, returns a single `NULL`, whereas `select val from info` would return an empty list. Since there is a possibility of a null response in the query, you'll need to accept a list of `int?`, or change the query. – David Hedlund Oct 25 '16 at 12:07

2 Answers2

15

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.

juharr
  • 31,741
  • 4
  • 58
  • 93
  • thx so FirstOrDefault doesn't help us in this situation? I have to have another specific null check (assuming I go the first route). – niico Oct 25 '16 at 12:16
  • Yes, the `OrDefault` part only applies to an empty result set (no rows). – juharr Oct 25 '16 at 12:18
  • Thanks - I keep forgetting that SQL aggregates can return null, and had incorrectly used `SELECT SUM(ISNULL(myCol, 0))` instead of `SELECT ISNULL(SUM(myCol), 0)` – StuartLC Apr 30 '19 at 18:39
  • Note to self: though `Single` is most appropriate for this particular query, you could still use the other variants like `FirstOrDefault`, `First`, etc. The actual problem is that Dapper never converts nulls returned from the database to values automatically, so it's up to you to handle them manually. – MarredCheese Apr 28 '21 at 19:21
3
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.

Vivek Nuna
  • 25,472
  • 25
  • 109
  • 197