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 ?
开发者_如何学GoThanks 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.
精彩评论