C# Entity Framework 4.1 Lambda Include - only select specific included values

I'm doing a lambda select on EF4.1, including another related DBSet in my current statement.

 return dbEntity.GameTypes.Include(a => a.Draws)
                           .Where(d => d.IsActive == true )
                           .ToList();

I've got two classes:

//simplified versions of the classes
public class GameType
{
 public Nullable<bool> IsActive { get; set; }
 public virtual ICollection<Draw> Draws { get; set; }
}

public class Draw
{
 public int DrawID { get; set; }
 public int GameTypeID { get; set; }
 public System.DateTime DrawDate { get; set; }
} 

But I only want the next upcoming draw for each GameType. Essentially I want to do something like

 return dbEntity.GameTypes.Include(a => a.Draws.Where(aw => aw.DrawDate > System.DateTime.Now)
                                               .OrderBy(ao => ao.DrawDate)
                                               .First())
                           .Where(d => d.IsActive == true )
                           .ToList();

But it gives me:

The Include path expression must refer to a navigation property defined on the type. Use dottedpaths for reference navigation properties and the Select operator for collection navigation propertie开发者_StackOverflows.

Is something like this possible or would I need to filter the result afterwards? I'd then also like to order the total result by the latest Draw.DrawDate. If anyone could show me the proper way I'd be trully gratefull.

I think....

    from g in dbEntity.GameTypes.Include("Draws")
   where g.IsActive
     let d = g.Draws.Where(o => o.DrawDate > System.DateTime.Now)
                    .OrderBy(o => o.DrawDate)
                    .Take(1)       // Needs to stay a collection
  select new GameType {IsActive = g.IsActive, Draws = d}

untested - but it might get you on the right path...

From MSDN for DbExtensions.Include().

The path expression must be composed of simple property access expressions together with calls to Select in order to compose additional includes after including a collection property.

So I don't think using Where() is allowed. I'm not sure if you can do any filtering when it comes to Include().

I've never been successful at finding a way to filter the children like you want to do. In order to reduce the volume of data being retrieved from the database I will usually retrieve just the parent objects and then loop over them getting only the children I want and "attach" them to the parent. That's not a hard-and-fast rule though, it kind of depends on how many children there are for each parent and what percentage of them I'm likely to want to keep.