C# Entity Framework Query...

Question

I'm trying to figure out how to limit my child dataset to only include active records...

    // Here's what I have currently...
    m_BackLoggerEntities.Stories
     .Include("Sprints")
     .Include("Tasks")
     .Include("Efforts")
     .Include("Products")
     .First(s => s.StoryId == id);


    // Here's what I thought I could do...
    m_BackLoggerEntities.Stories
     .Include("Sprints")
     .Include("Tasks")
     .Include("Efforts")
     .Include("Products")
     .Where(s => s.Tasks.Active)
     .First(s => s.StoryId == id);


    // I also tried this...
    m_BackLoggerEntities.Stories
     .Include("Sprints")
     .Include("Tasks")
     .Include("Efforts")
     .Include("Products")
     .First(s => s.StoryId == id && s => s.Tasks.Active));

Obviously none of these are working. I'm not sure how else to do this...

Answer 1

The only way I've found to "simulate" what I want is to use...

        var storyToDetail =
         m_BackLoggerEntities.Stories
          .Include("Sprints")
          .Include("Tasks")
   .Include("Efforts")
   .Include("Products")
          .First(s => s.StoryId == id);

Then in the foreach in the view...

   <% foreach (var task in Model.Tasks.Where(t => t.Active))

But this of course brings back a lot more records then I want.

Answer 2

Take a look at Alex James Tip 37. According to example in linked article, it can be done like this:

var query = from story in m_BackLoggerEntities.Stories
            where story.StoryId == id
            select new {
                          story,
                          Tasks = from task in story.Tasks
                                  where task.Active
                                  select task
                       };

var stories = query
   .AsEnumerable()
   .Select(x => x.Story);

Each Story inside of "stories" should have only active Tasks.