开发者

Select MAX from multiple Tables

I have four tables, and each table has a date column in it. I am trying to get MAX date of these four date columns.

    DateTime maxDate = ((from stockIn in db.StockIns select stockIn.StockInDate)
.Union(from stockOut in db.StockOuts select stockOut.StockOutDate)
.Union(from stockC in db.StockClearances select stockC.StockClearanceDate)
.Union(from stockR in db.StockRejections select stockR.StockRejectionDate))
.Max().Value; 

Is this the simplest way ?

开发者_如何学Go

Thanks in advance


If that's the way your data is stored, that's pretty much as simple as it gets.


I wouldn't use UNION for this problem as the query may have poor performance. Union of 4 tables and then sorting (to get the Max) might be really slow with big tables.

Either get 4 MaxDates from 4 queries and find max of them in the application or use 4 subqueries to get these 4 MaxDates and then either a (SQL syntax) CASE WHEN or a UNION of these results (which would be only 4 rows) and a MAX().

No idea how to translate either from SQL to Linq-to-sql ...


Ideally you would have your Stock all stored in one table, with linkages to other tables that say whether its In, Out, Clearance or Rejections. Then you could simply do a MAX on the one table.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜