问题描述
几天前,我将映射到两个类消息
和 MessageStatusHistory
使用EF。映射进行得很好,但是在类消息
中将导航属性 StatusHistory
code> MessageStatusHistory 对象。我正在加载一个用户的消息,只想要与该用户有关的状态。就像我想显示用户是否将标记的消息标记为已读/未读和何时。如果我使用默认加载机制,如下所示,它加载与消息相关的所有历史记录,无论用户如何:
IDbSet< Message> dbs = _repo.DbSet;
dbs.Include(StatusHistory)。其中(x => x.MessageIdentifier == msgIdentifier);
为了过滤一个用户的历史记录,我尝试了以下技巧:
IDbSet< Message> dbs = _repo.DbSet;
var q = from d in dbs.Include(StatusHistory)
其中m.MessageIdentifier == msgIdentifier
选择新消息
{
MessageIdentifier = m.MessageIdentifier ,
/ *其他属性* /
StatusHistory = m.StatusHistory
.Where(x => x.UserId == userId).ToList()
};
返回q.ToList(); //在此线上导致错误
我收到错误:
实体或复合类型MyLib.Biz.Message不能在LINQ
到实体查询。
我已经尝试通过评论 StatusHistory = m.StatusHistory.Where(x = > x.UserId == userId).ToList()
也没有帮助。
请帮助我获取已过滤的消息状态历史
编辑: - 以上代码已解决:
var q = from _ in _repository.DBSet.Include(Histories)
其中m.MessageIdentifier == id
select new {
m.Id, / *其他属性* /
历史= m.Histories.Where(x =>
x.SenderId == userId).ToList()
};
var lst = q.ToList();
return lst.Select(m => new Message {
Id = m.Id,MessageIdentifier = m.MessageIdentifier,
MessageText = m.MessageText,Replies = m.Replies,
ReplyTo = m.ReplyTo,Histories = m.Histories,SenderId =
m.SenderId,SenderName = m.SenderName,CreatedOn = m.CreatedOn
})ToList();
但是,如果我尝试包含回复邮件:
from _ in _repository.DBSet.Include(Replies)。Include(Histories)
我将错误转换为列表,其中 q.ToList()
Histories = m.Histories.Where(x => x.SenderId == userId).ToList()
。
IEnumerable< T>
并在构建消息
时转换为列表< T>
。您也不需要创建两个列表对象,您可以使用选择) > AsEnumerable(): var list =(from m in _repository.DBSet
其中m.MessageIdentifier == id
选择新的{
// ...
历史= m.Histories.Where(x => x.SenderId = = userId)
})
.AsEnumerable()//数据库查询在这里执行
.Select(m => new Message {
// ...
历史= m.Histories.ToList(),
// ...
})ToList();
返回列表;
请注意, Include
您使用选择
的投影。您需要使属性要包含投影的一部分,就像您已经使用选择新的{Histories ....
。
Few days back I put a question regarding mapping two classes Message
and MessageStatusHistory
using EF. The mapping is going fine but I am facing some problems with the navigation property StatusHistory
in class Message
that relates it to MessageStatusHistory
objects. I am loading the messages for one user only and want to the statuses pertaining to that user only. Like I would want to show if the user has marked message as read/not-read and when. If I use default loading mechanism like following it loads all the history related to the message irrespective of the user:
IDbSet<Message> dbs = _repo.DbSet;
dbs.Include("StatusHistory").Where(x=>x.MessageIdentifier == msgIdentifier);
To filter history for one user only I tried following trick:
IDbSet<Message> dbs = _repo.DbSet;
var q = from m in dbs.Include("StatusHistory")
where m.MessageIdentifier == msgIdentifier
select new Message
{
MessageIdentifier = m.MessageIdentifier,
/*OTHER PROPERTIES*/
StatusHistory = m.StatusHistory
.Where(x => x.UserId == userId).ToList()
};
return q.ToList();//THROWING ERROR ON THIS LINE
I am getting the error:
The entity or complex type 'MyLib.Biz.Message' cannot be constructed in a LINQ
to Entities query.
I have tried by commenting StatusHistory = m.StatusHistory.Where(x => x.UserId == userId).ToList()
also but it has not helped.
Please help me in getting Messages with filtered StatusHistory.
EDIT:- above is resolved with this code:
var q = from m in _repository.DBSet.Include("Histories")
where m.MessageIdentifier == id
select new {
m.Id,/*OTHER PROPERTIES*/
Histories = m.Histories.Where(x =>
x.SenderId == userId).ToList()
};
var lst = q.ToList();
return lst.Select(m => new Message{
Id = m.Id, MessageIdentifier = m.MessageIdentifier,
MessageText = m.MessageText, Replies = m.Replies,
ReplyTo = m.ReplyTo, Histories = m.Histories, SenderId =
m.SenderId, SenderName = m.SenderName, CreatedOn = m.CreatedOn
}).ToList();
But if I try to include replies to the message with:
from m in _repository.DBSet.Include("Replies").Include("Histories")
I am getting error on converting query to List with q.ToList()
for Histories = m.Histories.Where(x=> x.SenderId == userId).ToList()
.
About your EDIT part: You cannot use ToList()
in a projection, just leave it an IEnumerable<T>
and convert to a List<T>
when you construct the Message
. You also don't need to create two list objects, you can switch from the LINQ to Entities query to LINQ to Objects (the second Select
) by using AsEnumerable()
:
var list = (from m in _repository.DBSet
where m.MessageIdentifier == id
select new {
// ...
Histories = m.Histories.Where(x => x.SenderId == userId)
})
.AsEnumerable() // database query is executed here
.Select(m => new Message {
// ...
Histories = m.Histories.ToList(),
// ...
}).ToList();
return list;
Be aware that Include
has no effect when you use a projection with select
. You need to make the properties that you want to include part of the projection - as you already did with select new { Histories....
.
这篇关于加载导航属性与实体框架4.3的过滤器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!