加载导航属性与实体框架4.3的过滤器 [英] load navigation properties with filter for Entity Framework 4.3

问题描述

几天前，我将问题映射到两个类消息和 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（）。

解决方案<关于您的编辑部分：您不能在投影中使用 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的过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！