Entity Framework: Why do navigation properties disappear after a group by?

Question

I retrieve a collection with the following query:

var numbers = _betDetailItem.GetBetDetailItems().Where(betDetailItem => betDetailItem.BetDetail.Bet.DateDrawing == resultToCreate.Date && betDetailItem.BetDetail.Bet.Status == 1).Where(condition);

Right there I'm able to access my navigation properties and navigate through binded info. Note how I actually use them to filter the data.

After I group the results, the navigation properties become null.

var grouped = numbers.GroupBy(p => p.BetDetail.Bet);
//Iterate through the collection created by the Grouping
foreach (IGrouping<Bet, BetDetailItem> group in grouped)
{
    var details = group.Key.BetDetails; //This is what doesn't work. BetDetails is a navigation property which was accessible in the previous query. 
}

Am I doing something wrong?

Answer 1

Once you do a GroupBy(), you're no longer dealing with your entities -- they have been... well, grouped, so the var in var grouped = ... is now of type IEnumerable<IGrouping<.... As such, the methods available on the items in the grouped collection are the methods of the IGrouping<> interface.

You may want to OrderBy() instead of GroupBy(), depending on your need, or you'll need to iterate on two levels: iterate over each group in grouped, and over each member within each of those.

Once you are inside of a particular IGrouping<>, you should have access to the properties for which you are looking.

Answer 2

You are confusing LINQ to Entities and object operations.

This is LINQ to Entities:

var numbers = _betDetailItem.GetBetDetailItems().Where(betDetailItem => betDetailItem.BetDetail.Bet.DateDrawing == resultToCreate.Date && betDetailItem.BetDetail.Bet.Status == 1).Where(condition);

So is this:

var grouped = numbers.GroupBy(p => p.BetDetail.Bet);

These are object operations:

foreach (IGrouping<Bet, BetDetailItem> group in grouped)
{
    var details = group.Key.BetDetails; //This is what doesn't work. BetDetails is a navigation property which was accessible in the previous query. 
}

In LINQ to Entities, there is never any need to think about loading related instances. You can always refer to any property of any object. However, at some point, you want to move out of the LINQ to Entities world and into object space, because you want to work with instances of type BetDetail instead of type IQueryable<BetDetail>. This means that the Entity Framework is now required to generate SQL to retrieve data from the database. At that point, it doesn't snow which related instances you will be accessing in your code later on. Nothing in your LINQ to Entities query forces the loading of the related Bet. So unless you do something to cause it to be loaded, like use eager loading, explicit loading, or EF 4 lazy loading, it won't be loaded.

Using lazy loading (e.g., in Entity Framework 4, or in another ORM) will make this code appear to function, but it will be unnecessarily slow, due to the large number of database queries generated. A better solution would be to use eager loading or projection. This way there will be only one DB roundtrip.