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

Question

I am making a lambda selection on EF4.1, including another related DBSet in my current application.

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

I have 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 want only 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 specify the navigation property defined in the type. Use dottedpath for reference navigation properties and the Select statement for collection navigation properties.

Is something like this possible or will I need to filter the result afterwards? I would also like to order the overall result from the latest version of Draw.DrawDate. If someone could show me the right path, I would love to work.

Answer 1

....

    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 - ...

Answer 2

MSDN DbExtensions.Include().

.

, Where() . , , Include().

Answer 3

I never managed to find a way to filter the children as you want. To reduce the amount of data retrieved from the database, I usually get only the parent objects, and then iterate over them, getting only the children that I want, and “attach” them to the parent. This is not a strict rule, although it depends on how many children there are for each parent and what percentage of them I probably want to keep.