Constraint on Linq to Entities Include

I have a message table that self joins to itself where Message.ID == Message.Parent. So I have 1 Message with several ChildMessages. This is set up nicely with a Navigation property.

The code is currently:

var message = from m in soe.Messages.Include("ChildMessages")
              where m.ID == id
              && m.IsActive
              select m;
return message.FirstOrDefault();

What I really want is to only return 1 Message (that is equal to a particular开发者_运维技巧 ID and isActive) with all of its Active ChildMessages and I want to do it in one call to the database.

I have a solution for 4.0 (I'm not sure if it will work on 3.5, i hadn't check).

First, set your entities model Lazy Loading Enabled property to false. Note that as a result you will lose the references auto-loading.

Next, try this code:

var message = soe.Messages
    .Where(m => (m.ID == id || m.ParentId == id) && m.IsActive)
    .AsEnumerable()
    .FirstOrDefault(e => e.Id == id);

The EF will resolve the parent-child references, and will not load other child references unless an explicit request will be made (like Include or Load). Notice that you must iterate over the Where results using AsEnumerable or otherwise they won't load.

I've concluded this is not possible in Entity framework.

A work around would be to return an anonymous type that satisfies the constraint. E.g.

var message = from m in soe.Messages
              where m.ID == id
              && m.IsActive
              select
                  new
                  {
                      Message = m,
                      ChildMessages = m.ChildMessages.Where(c => c.IsActive)
                  };
return message.FirstOrDefault()