我有一些类定义了具有关系的实体
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":"发生错误。","exceptionMessage":"Include路径表达式必须引用在类型上定义的导航属性。使用虚线路径表示引用导航属性,使用Select运算符表示集合导航属性…………..}
我的思想抗拒理解和学习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)
.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();
}
不能在Include上进行投影。include就是热切加载。C#中的输出没有变化。只有最初加载的数据量(即性能(会发生变化。
你似乎想要一个投影,而不是急于加载,这是完全不兼容的概念。
然而,我不明白你到底想达到什么目的。
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.Select(_=>_))
.Include(c => c.Messages.Select(_=>_))
.ToListAsync();
}
应该足够了。