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

Question

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.

You can do .Include on navigational properties only. Peter Hancock's query is fine, except you do not need to do an Include in that query. — AD.Net, Jun 21 '11 at 02:34
I think that depends on whether lazy loading and proxy creation are enabled - if they are, the Include isn't needed - if they're not, then the include is — Pete - MSFT, Jun 21 '11 at 03:12
From EntityFramework 4.1 you must use System.Data.Entity: using System.Data.Entity ... MyDBContext.MySet.Include(o => o.MyAssociation) — Antonio Bardazzi, Sep 13 '12 at 09:44

score 2 · Answer 1 · answered Jun 21 '11 at 02:19

2

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

answered Jun 21 '11 at 02:19

Pete - MSFT

4,249
1
21
38

I think this would still bring down all the associated `Draws`. – Bala R Jun 21 '11 at 02:21
I don't think so - Draws is being filtered by DrawDate > Now, and then ordered, before `Take(1)` – Pete - MSFT Jun 21 '11 at 02:28
You do not need to do .Include here in the first line – AD.Net Jun 21 '11 at 02:35
That depends on whether the context has lazy loading and proxy creation turned on. Given that the original request had the include, I'd suggest that proxy creation and lazy loading is turned off => requiring the include. – Pete - MSFT Jun 21 '11 at 02:41
Thanks Peter. It did give a few errors, but decided to not spend too much time playing with it. In terms of performance and what I need it to do, I decided best would just be to create a SProc and a relating complex type. – Gido Jun 21 '11 at 05:25
This is mostly correct answer. You cannot filter or order included related entities. If you want to filter or order related entities you must use projection. Once you use projection you don't need `Include`. – Ladislav Mrnka Jun 21 '11 at 07:20

score 0 · Answer 2 · answered Jun 21 '11 at 02:17

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

score 0 · Answer 3 · answered Jun 21 '11 at 02:19

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.

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

3 Answers3

Linked