Best Linq2Sql equivalent of IsNull(Max(id))

Question

I'm looking to convert the following SQL into Linq2SQL.

select isnull(max(Id),0) from tbl

Even though I have Id defined as Int NOT NULL

I wish to be able to have a defult value of 0, even when there are no rows in the table.

The best readable approach I've been able to come up with is

var maxId = dc.tbl.Select(row => row.Id)
                  .ToArray().Union(Enumerable.Range(0, 1)).Max();

But this approach requires bringing down all the data, a more performant but less readable version is

var maxId = dc.tbl.Select(row => row.Id)
                  .OrderByDescending(ii => ii).FirstOrDefault();

Is there a better way?

Answer 1

How about something like this:

from t in tbl
select new {
  ID = ((System.Int32?)t.ID ?? (System.Int32?)0)
}

Answer 2

Maybe something like this?

var maxId = dc.tbl.Any() ? dc.tbl.Max(e => e.Id) : 0;

According to this question, you may also do something like this:

var maxId = (from e in dc.tbl
            select (int?)e.Id).Max() ?? 0;

Answer 3

I would use something like the following.

context.Table.Max(row => (Int32?)row.Id) ?? 0

And by the way, I think your second suggestion is much more clear about the purpose than the first one.

UPDATE

I just tested the query with LINQPad. Should LINQPad cause the query to work while it does not in code, your second suggestion is probably the best solution.

context.Table
   .Select(row => row.Id)
   .OrderByDescending(id => id)
   .FirstOrDefault()

The drawback is that the default value is fixed at zero. This can be changed with the following query.

context.Table
   .Select(row => (Int32?)row.Id)
   .OrderByDescending(id => id)
   .FirstOrDefault() ?? 0

And just to note, the following does not work, because LastOrDefault() is not supported by LINQ to SQL.

context.Table
   .Select(row => row.Id)
   .OrderBy(id => id)
   .LastOrDefault()