Constraint on Linq to Entities Include

Question

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.

Answer 1

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.