Linq include with where clause

Question

Hey so I've got the situation where I'm pulling a client back from the database and including all the case studies with it by way of an include

return (from c in db.Clients.Include("CaseStudies")
        where c.Id == clientId
        select c).First();

but what I want to do now is and a where clause on the included casestudies so that it only returns the case studies where deleted = false

sort of like this

return (from c in db.Clients.Include("CaseStudies")
        where c.Id == clientId
        && c.CaseStudy.Deleted == false
        select c).First();

But this doesn't work :( any ideas

Solution 1

Conditional includes are not supported out-of-the-box in EF v1.0. But Alex James has a bit hacky workaround for that explained well here: http://blogs.msdn.com/alexj/archive/2009/10/13/tip-37-how-to-do-a-conditional-include.aspx

 var dbquery =
   from c in db.Clients
   where c.Id == clientID
   select new {
      client = c, 
      caseStudies = from cs in c.CaseStudy
                where cs.Deleted==false
                select cs
   };

return dbquery
   .AsEnumerable()
   .Select(c => c.client);

Also, I haven't succeeded to make this workaround work with many-to-many relationships.

Solution 2

You can return a similar group of records this way, the GroupBy is going to make the enumeration different, but its not difficult.

CaseStudies.Include("Client")
           .Where(c => !c.Deleted && c.Client.ID == ClientID)
           .GroupBy(c => c.Client.ID);

Comments

[JoeBrockhaus]

This would've been my suggestion. GroupBy will work, but you could also just Select out the Client. So, you'd wind up with an IEnumerable<Client>. Also, slap a .Distinct() onto the end (EF knows what makes a Client distinct so you don't need a special IEqualityComparer<Client>). Client.CaseStudies will be populated on each because of Navigation Properties.

[JoeBrockhaus]

The better answer is query on CaseStudies, including Client, then Select Client, slap .Distinct().ToList(), and voila! Client.CaseStudies will be populated automatically through Navigation properties.

[Vladimirs]

I think it also worth to mention that AsNoTracking will prevent EF from doing relationship fix-up magic (e.g. from c in db.Clients.AsNoTracking())