我想将以下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 tblEven 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) ?? 0And 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() ?? 0And 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()更多推荐
发布评论