如何在c# linq查询中选择表的最大日期
本文关键字：日期 选择 linq 查询 | 更新日期: 2023-09-27 18:14:45
我是堆栈在这里，我需要有人帮助请。我想从不同的表中获得最大日期，其中Refno等于Refno。我当前的代码是给我所有的日期，存在于actionrecords表。
我的当前代码看起来像
public IQueryable<EmployerDetails> _employerProfileOverview(string refNo, string organisationname, string tradingname)
{
var query = (from _employerdetails in _entities.Organisations
join _lasttransaction in _entities.LevyTransactions on _employerdetails.Refno equals _lasttransaction.Refno
select new EmployerDetails
{
RefNo = _employerdetails.Refno,
Surname = _employerdetails.Surname,
TradingName = _employerdetails.TradingName,
Status = _employerdetails.Status,
TransactionDate = _lasttransaction.TransactionDate
});
if (string.IsNullOrEmpty(refNo).Equals(false))
query = query.Where(x => x.RefNo.Contains(refNo.Trim()));
if (string.IsNullOrEmpty(organisationname).Equals(false))
query = query.Where(x => x.Surname.Contains(organisationname.Trim()));
if (string.IsNullOrEmpty(tradingname).Equals(false))
query = query.Where(x => x.TradingName.Contains(tradingname.Trim()));
return query;
}
我的输出是这样的
Refno TradingName Status TransactionDate
L940732953 PSG KONSULT BPK LICHTENBURG A 2011-01-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-03-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-04-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-05-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-06-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-07-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-08-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-09-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-10-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-11-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2012-12-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-01-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-02-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-03-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-04-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-05-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-05-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-06-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-11-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2013-12-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-01-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-02-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-03-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-04-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-05-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-06-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-07-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-08-01 00:00:00.00
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-09-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-10-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-11-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2014-12-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2015-01-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2015-02-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2015-03-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2015-04-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2015-05-01 00:00:00.000
L940732953 PSG KONSULT BPK LICHTENBURG A 2015-06-01 00:00:00.000
L940733035 BROUGHTON AND JONCK A 2011-05-01 00:00:00.000
L940733035 BROUGHTON AND JONCK A 2011-05-01 00:00:00.000
L940733035 BROUGHTON AND JONCK A 2011-05-01 00:00:00.000
L940733035 BROUGHTON AND JONCK A 2011-05-01 00:00:00.000
L940733035 BROUGHTON AND JONCK A 2011-05-01 00:00:00.000
L940733035 BROUGHTON AND JONCK A 2011-05-01 00:00:00.000
L940736574 COVERS FOODS CC Y 2011-03-01 00:00:00.000
L940736574 COVERS FOODS CC Y 2011-03-01 00:00:00.000
L940736574 COVERS FOODS CC Y 2011-05-01 00:00:00.000
L940736574 COVERS FOODS CC Y 2011-05-01 00:00:00.000
L940738141 TEAMWORX SHOPFITTERS (PTY) LTD A 2011-01-01 00:00:00.000
L940738141 TEAMWORX SHOPFITTERS (PTY) LTD A 2011-02-01 00:00:00.000
L940738141 TEAMWORX SHOPFITTERS (PTY) LTD A 2011-02-01 00:00:00.000
L940738141 TEAMWORX SHOPFITTERS (PTY) LTD A 2011-04-01 00:00:00.000
L940738141 TEAMWORX SHOPFITTERS (PTY) LTD A 2011-05-01 00:00:00.000
我只想显示max(Transaction)仅每个Refno。
请帮帮我，伙计们。
您需要在这里使用group by并使用Max扩展方法来查找最大交易日期:-
var query = (from _employerdetails in _entities.Organisations
join _lasttransaction in _entities.LevyTransactions
on _employerdetails.Refno equals _lasttransaction.Refno
group new { _employerdetails, _lasttransaction }
by new { _employerdetails.Refno,
_employerdetails.Surname_employerdetails.TradingName,
_employerdetails.Status } into g
select new EmployerDetails
{
RefNo = g.Key.RefNo ,
Surname = g.Key.Surname,
TradingName = g.Key.TradingName,
Status = g.Key.Status,
TransactionDate = g.Max(x => x._lasttransaction .TransactionDate)
});
另外，作为旁注，因为String。IsNullOrEmpty返回布尔值，不需要与false或true比较。你可以直接这样做:-
if (!string.IsNullOrEmpty(refNo))
query = query.Where(x => x.RefNo.Contains(refNo.Trim()));
您可以在return语句之前添加以下行:
query = query.OrderByDescending(q => q.TransactionDate).First();
另一个选择是使用MoreLinq中的Max by属性函数，这将使代码更优雅。
还有，你应该写:
if (!string.IsNullOrEmpty(refNo))