Correct option for handle MAX() when table is empty? [duplicate]

Question

When table is empty I guess all three perfomance is similar time. But not sure what will be the performance impact when table has million of records.

1 . Original query isnt affected, but dont like because have to make two separated db consults.

2 . Dont like because when no empty table have has to bring all the rows instead of using the index to find the max.

3 . Well this should return null instead of raising an error.

long? LastInsertedID;

try
{
  LastInsertedID = db.myTable.Any() ? 
                       db.myTable.Max(p => p.id) 
                     : 0;

  LastInsertedID = db.myTable.Select(p => p.id)
                             .DefaultIfEmpty(0)
                             .Max();

  LastInsertedID = db.myTable.Max(p => p.id); -- cause Exception
}

Exception:

{"The cast to value type 'System.Int64' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."}

Answer 1

IMO the working third option is the best. Database queries return null for most of the aggregate functions (except COUNT) when the set is empty, so is the suggestion by the exception message.

All you need is to include a cast to the corresponding nullable type:

LastInsertedID = db.myTable.Max(p => (long?)p.id);

Answer 2

The second is better. You avoid multiple database hits (or hits on the collection in general), it is clearer, and it can't fail.