C# Entity Framework 4.1 Lambda Include - only sele

问题:

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

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.

回答1:

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

回答2:

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

回答3:

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.