LINQ 包含和投影

Question

我有一些类定义具有关系的实体

Account 
has many Conversations [IEnumerable<Conversation> Conversations]

Conversation 
has many Participants [IEnumerable<Account> Participants]
has many Messages [IEnumerable<Message> Messages]

Message 
has one Sender [Account Sender]
has one Conversation [Conversation Conversation]

我正在尝试编写一个 LINQ 查询，该查询返回按日期排序的对话列表，包括相关参与者和消息。

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(p => p.AccountId == id))
         .Include(c => c.Participants)
         .Include(c => c.Messages)
         .ToListAsync();
}

这完成了工作，但包含了很多我并不真正需要的数据。

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(a => a.AccountId == id))
         .Include(c => c.Participants.Select(a=> new 
                       {
                          AccountId = a.AccountId,
                          Profile = new { FullName = a.Profile.FullName,
                                          Email = a.Profile.Email
                                        }                        
                       }))
         // Only return the last message in 
         // Eventually I would not return an array with a single object but just the single object inside associated with the property LastMessageIn
         .Include(c => c.Messages.OrderBy(m => m.Date).Select(m=> new 
                       {
                          Body = m.Body,
                          SenderId = m.Sender.AccountId
                       }).Last())
         .ToListAsync();
}

此脚本返回一英里长的异常

{"message":"An error has occurred.","exceptionMessage":"The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties........}

我的想法抵制理解和学习 LINQ 我不知道是不是只有我一个人，但一旦需求超出了基本的查询和预测，它就会脱离我的控制

有人有一些提示吗？

Answer 1

我不确定我是否理解你的问题，但我相信你想要这样的东西:

public async Task<List<Conversation>> FindAllByAccountIdAsync(Int32 id)
{
    return await _Db.Conversations
         .Where(c => c.Participants.Any(p => p.AccountId == id))
         .Include(c => c.Participants)
         .Include(c => c.Messages)
         .Select(c => new 
         {
            Participants = c.Participants.Select(a=> new 
                       {
                          AccountId = a.AccountId,
                          Profile = new { FullName = a.Profile.FullName,
                                          Email = a.Profile.Email
                                        }                        
                       },
             //EDIT: using OrderByDescending and FirstOrDefault
             Messages = c.Messages.OrderByDescending(m => m.Date).Select(m=> new 
                       {
                          Body = m.Body,
                          SenderId = m.Sender.AccountId
                       }).FirstOrDefault())
             //others properties here
         }
         .ToListAsync();
}