Linq when using GroupBy, Include is not working

Question

include matchparticipants is not working. It always says Null when I debug. But when I put the GroupBy in comment it works fine. I am using Entity framework 4.3.1 with code-first.

Entities:

public class Match
    {
        [ScaffoldColumn(false)]
        public int MatchId { get; set; }

        [Required(ErrorMessage = "Matchtype is a required field")]
        public int Scheme { get; set; }

        [Required]
        [DefaultValue(false)]
        public bool Finished { get; set; }

        public int Round { get; set; }


        // Relations
        [Required]
        public Category Category { get; set; }

        public Official Official { get; set; }

        public Slot Slot { get; set; }

        public ICollection<MatchParticipant> MatchParticipants { get; set; }
    }

 public class MatchParticipant
    {
        [ScaffoldColumn(false)]
        public int MatchParticipantId { get; set; }

        public int Points { get; set; }

        public int Goals { get; set; }

        [Required]
        public Match Match { get; set; }

        [Required]
        public Team Team { get; set; }
    }

public class Team
    {
        [ScaffoldColumn(false)]
        public int TeamId { get; set; }

        [Required(ErrorMessage="Name is a required field")]
        public string Name { get; set; }

        [Required(ErrorMessage="Number of players is a required field")]
        public int NumberOfPlayers { get; set; }

        [Required(ErrorMessage="Coach is a required field")]
        public string Coach { get; set; }

        [Required(ErrorMessage="Phone is a required field")]
        public string Phone { get; set; }

        public string CellPhone { get; set; }

        public string Fax { get; set; }

        [Required(ErrorMessage="Email is a required field")]
        public string Email { get; set; }

        [Required(ErrorMessage="Address is a required field")]
        public Address Address { get; set; }

        public Pool Pool { get; set; }

        [Required(ErrorMessage = "Category is a required field")]
        public Category Category { get; set; }

        public ICollection<MatchParticipant> matchParticipants { get; set; }
    }

        var matches =
        context.matches
       .Include("Official")
       .Include("Slot")
       .Include("MatchParticipants.Team")
       .Include("Category.Tournament")
       .Where(m => m.Category.Tournament.TournamentId == tournamentId)
       .GroupBy(m => m.Category);

How can I make the Include work?

Answer 1

Include demands that the shape of the query doesn't change. It means that your query must return IQueryable<Match>. GroupBy operator is probably considered as shape changing because it returns IQueryable<IGrouping<TKey, TSource>>. Once the shape of the query changes all Include statements are omitted. Because of that you cannot use Include with projections, custom joins and groupings.

As a workaround you can execute grouping in Linq-to-objects:

var matches = context.matches
                     .Include("Official")
                     .Include("Slot")
                     .Include("MatchParticipants.Team")
                     .Include("Category.Tournament")
                     .Where(m => m.Category.Tournament.TournamentId == tournamentId)
                     .ToList()
                     .GroupBy(m => m.Category);

Edit: As mentioned in comments and in other answer, this is very dangerous workaround which can lead to performance problems. It pulls all records from database to the application and makes the aggregation in the app. It can work in same cases but it is definitely not applicable as generic solution.

Answer 2

In this special case, when your GroupBy is the latest operator, this query works good... But IMHO answer above is the worst answer for beginer, because it causes really badly optimized query when your GroupBy is not executed right after it, but is followed by some other statement (Where, Select...).

var ctx = new MyDataContext(); // Please use "using"
var result = ctx.SomeTable
                //.Include(ah => ah.IncludedTable) // DO NOT PUT IT HERE
                .Where(t => t.IsWhateverTrue)
                .GroupBy(t => t.MyGroupingKey)
                .Select(gt => 
                    gt.OrderByDescending(d => d.SomeProperty)
                        .FirstOrDefault(ah => ah.SomeAnotherFilter))
                .Include(ah => ah.IncludedTable) // YES, PUT IT HERE
                .ToList(); // Execute query here

Answer 3

Specify the includes in the .Select(), after GroupBy(). This will include them in the result:

var result = ctx.SomeTable
                .Where(t => t.IsWhateverTrue)
                .GroupBy(t => t.MyGroupingKey)
                .Select(g => new
                  {
                    Date = g.Key.Value,
                    Reservations = g.Select(m => new
                        {
                          m,
                          m.IncludedTable // this selects/includes the related table
                        })
                  }
                );

Answer 4

I can't find a working way to use Entity Framework to Group on the SQL side, and then Include() on the .net site. An alternative is to write your own SQL query and do your own mapping. This actually isn't that hard:

1. Run Install-Package dapper in the Nuget Package Manager Console for entity mapping

2. Define your custom object to map to (or use an existing one)

   class MyClass { int userID; int numRows;}
   

3. Query against your context object:

   List<MyClass> results = _context.Database.GetDbConnection().Query<MyClass>("SELECT u.id as userID, count(1) as numRows FROM Users u inner join Table2 t2 on u.id= t2.userID  group by u.id;").ToList();
   

Answer 5

You can implement the Include in the .Select() after GroupBy() like this:

var query = ctx.Table
   .Where(x => "condition")
   .GroupBy(g => g.GroupingKeyId)
   .Select(x => new
   {
       GroupingKeyId = x.Key.GroupingKeyId,
       GroupingKey = x.Select(y => y.GroupingKey)
            .FirstOrDefault(y => y.Id == x.Key.GroupingKeyId)
   });