Linq GroupBy throws ArgumentOutOfRangeException

Question

I have these two tables

Animals           Activities
+----+-------+    +----+------------+----------+------------+
| Id | Name  |    | Id | Activity   | FkAnimal | Date       |
+----+-------+    +----+------------+----------+------------+
| 1  | Cats  |    | 1  | Ball       | 2        | 2015-05-21 |
+----+-------+    +----+------------+----------+------------+
| 2  | Dogs  |    | 2  | Pet        | 2        | 2015-06-07 |
+----+-------+    +----+------------+----------+------------+
| 3  | Birds |    | 3  | Running    | 1        | 2014-11-03 |
+----+-------+    +----+------------+----------+------------+
                  | 4  | Kill a fly | 1        | 2014-08-05 |
                  +----+------------+----------+------------+
                  | 5  | Kill a fly | 3        | 2014-08-05 |
                  +----+------------+----------+------------+

What I want is the result of this query

SELECT Animals.Name, Animals.Id, Activities.Data
FROM Activities
INNER JOIN Animals ON Animals.Id = Activities.Id
GROUP BY Animals.Name, Animals.Data

In LINQ from the Entity Framework

Here's my attempt:

//My repository is of type IRepository<Activities>
var list = Repository.GetAll().GroupBy(a => a.Animals).Select((grouping,i) => new {
    name = grouping.Key.Name,
    id = grouping.Key.Id,
    data = grouping.ElementAt(i).Data
}).ToList();

Unfortunately the ToList() method generate ArgumentOutOfRangeException, and if I debug the lambda it shows that i goes out of range

Answer 1

The i in .Select((grouping,i) => is the index of the group. In your example, .GroupBy(a => a.Animals) will return an IGrouping which is, essentially, just an IEnumerable with a Key property. The result from .GroupBy(a => a.Animals) will, loosely, look something like this (not sure exactly what your DbContext looks like):

{[
    {
        Key: Dogs
        GetEnumerator(): [
            {
                Id: 1
                Activity: Ball
                Date: 2015-05-21
            },
            {
                Id: 2
                Activity: Pet
                Date: 2015-06-07
            }
        ]
    },
    {
        Key: Cats
        GetEnumerator(): [
            {
                Id: 3
                Activity: Running
                Date: 2014-11-03
            },
            {
                Id: 4
                Activity: Kill a fly
                Date: 2014-08-05
            }
        ]
    },
    {
        Key: Birds
        GetEnumerator(): [
            {
                Id: 5
                Activity: Kill a fly
                Date: 2014-08-05
            }
        ]
    }
]}

The Select method is iterating over the groups, not the elements in the group. So the i in .Select((grouping,i) =>, in this case, refers to the index of the group (there are three groups) not an element in a group. Within your select you are calling data = grouping.ElementAt(i).Data, grouping in this case is an IGropuing which is an IEnumerable so ElementAt(i) is asking for the ith element in whichever group is currently being evaluated. By the time you get the third group i will be 2 but there is only one element in the group, hence the exception; at least in this example, your groups may come back in a different order but the principle is the same.

You probably want something like this:

var list = 
    Repository
    .GetAll()
    .GroupBy(a => a.Animals)
    .Select(grouping => new {
        name = grouping.Key.Name,
        id = grouping.Key.Id,
        data = grouping.Select(x => x)
    }).ToList();

Answer 2

does this work...

var res= from act in Repository.GetAll()
       let anm=act.Animals.Single(a=>a.Id=act.FkAnimal)
       select new {
          anm.Id, anm.Name, act.Activity
       };