c# - LINQ Join 从第二张表中获取最新记录

Question

问题:

它们是两个表 TableEmails 和 TableSentEmailRecords。因此，每当发送电子邮件时，都会在 TableSentEmailRecords 中存储一条记录，例如发送时间等。

我想做一个 LINQ 查询，它让我获得 TableEmails 的列以及 TableSentEmailRecords 的“SentDate”列，它显示电子邮件发送的最新日期。

我试过这个:

List<CustomEmail> emails = new List<CustomEmail>();

var query = from c in db.TableEmails 
            join o in db.TableSentEmailRecords on c.EmailId equals o.EmailId
                        select new CustomEmail
                        {
                            EmailName = c.EmailName,
                            EmailId= c.EmailId,
                            EmailDescription = c.EmailDescription,
                            LastDateEmailSentOut = o.SentDate
                        };

emails = query.ToList();

但这不一定会从 TableSentEmailRecords 表中获取该特定电子邮件的最新记录。

有什么想法吗？？？

Answer 1

您可以只使用嵌套查询而不是连接:

var query = from c in db.TableEmails 
            select new CustomEmail
            {
                EmailName = c.EmailName,
                EmailId= c.EmailId,
                EmailDescription= c.EmailDescription,
                LastDateEmailSentOut = db.TableSentEmailRecordson
                                         .Where(x => x.EmailId = c.EmailId)
                                         .Max(x => x.SentDate)
            };