最好的Linq2Sql相当于IsNull（Max（id））(Best Linq2Sql equivalent of IsNull(Max(id)))

最好的Linq2Sql相当于IsNull（Max（id））(Best Linq2Sql equivalent of IsNull(Max(id)))

我想将以下SQL转换为Linq2SQL。

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

即使我将Id定义为Int NOT NULL

我希望能够将defult值设置为0，即使表中没有行也是如此。

我能想出的最好的可读方法是

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

但是这种方法需要降低所有数据，性能更高但可读性更低

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

有没有更好的办法？

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?

最满意答案

我会使用类似下面的内容。

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

顺便说一下，我认为你的第二个建议比第一个建议更明确。

UPDATE

我刚用LINQPad测试了查询。 如果LINQPad导致查询无法在代码中工作，那么您的第二个建议可能是最佳解决方案。

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

缺点是默认值固定为零。 可以使用以下查询更改此设置。

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

并且要注意，以下方法不起作用，因为LINQ to SQL不支持LastOrDefault() 。

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

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()