Entity Framework .Where nested in .Include

ef-code-first entity-framework linq-to-entities repository-pattern


I'm attempting to perform a db lookup using EF5 code-first. The basic structure and table relationships are as follows;

public partial class Member
    public int    RecordID {get; set;}
    public string Name     {get; set;}
    public virtual ICollection<MemberLink> MasterLinks {get; set;}
    public virtual ICollection<MemberLink> SlaveLinks {get; set;}
    public virtual ICollection<Message>    ReceivedMessages {get; set;}
    public virtual ICollection<Message>    SentMessages {get; set;}

public partial class MemberLink
    public int            RecordID     {get; set;}
    public virtual Member MasterMember {get; set;}
    public virtual Member SlaveMember  {get; set;}

public partial class Message
    public int            RecordID  {get; set;}
    public virtual Member Sender    {get; set;}
    public virtual Member Recipient {get; set;}

Now, the query I'm trying to perform is using the MemberLinkRepository, and looks like;

public IList<MemberLink> GetMasterLinks(int p_MemberID)
    return Get()
           .Include ( memberLink => memberLink.MasterMember )
           .Include ( memberLink => memberLink.SlaveMember )
           .Include ( memberLink => memberLink.MasterMember.ReceivedMessages
                      msg => msg.Sender.RecordID == memberLink.SlaveMember.RecordID) )
           .Where ( memberLink => memberLink.MasterMember.RecordID == p_MemberID)

Except EF doesn't seem to like the nested Where. I could split this out into 2 separate repository calls (and indeed, it's looking like I might have to do that) but in the interest of reducing calls to the db I'm trying to do it in one foul swoop. Does anyone know how I can achieve this in one single query?

I hope the code illustrates what I'm trying to do... If not, I'll try and explain a little better.

9/10/2013 1:20:18 AM

Accepted Answer

The short answer is no, EF will not let you do that using Include().

Think about the result if it let you do this: in one case your MemberLink.MasterMember.ReceivedMessages will be fully populated, on another identical looking object MemberLink.MasterMember.ReceivedMessages is actually a sub-set of messages! What happens if you try to add to the ReceivedMessages? What if the addition doesn't match the filter? It is a bag of hurt.

The answer is to use projections:

public IList<MemberLinkWithFiltereredMessages> GetMasterLinks(int p_MemberID)
    return Get()
        .Include(memberLink => memberLink.MasterMember)
        .Include(memberLink => memberLink.SlaveMember)
        .Where(memberLink => memberLink.MasterMember.RecordID == p_MemberID)
        .Select(memberLink => new MemberLinkWithFilteredMessages
            MemberLink = memberLink,
            FilteredMessages = memberLink.MasterMember.ReceivedMessages
                .Where(msg => msg.Sender.RecordID == memberLink.SlaveMember.RecordID)

What you are really doing is asking for a specific sub-set of information, so be explicit about it.

9/11/2013 10:52:04 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow