Group By + Select into New Nested Class Structure

Question

+----+---------+--------+
| id | animal  | type   |
+----+---------+--------+
|  0 | dog     | TypeA  |
|  1 | cat     | TypeA  |
|  2 | dog     | TypeB  |
|  3 | cat     | TypeB  |
+----+---------+--------+

Obviously what I want to do is using GroupBy in EF which somehow like this:

dbAnimal.GroupBy(x => x.animal);

But what if I want to furtherly map the data into the below class structure?

public class Animal
{
    public Animal()
    {
        this.animalType = new List<Type>();
    }

    public string animalDesc { get; set; }
    public List<Type> animalType { get; set; }

    public class Type
    {
        public string typeDesc { get; set; }
    }
}

Example in JSON format:

[
    {
        "animalDesc": "dog",
        "animalType": 
        [
            {
                "typeDesc": "TypeA",
            },
            {
                "typeDesc": "TypeB",
            }
        ]
    },
    {
        "animalDesc": "cat",
        "animalType": 
        [
            {
                "typeDesc": "TypeA",
            },
            {
                "typeDesc": "TypeB",
            }
        ]
    }
]

My question is how to achieve the mapping to class model after using GroupBy in EF.

Other workaround are also welcome too. Thanks.

possible duplicate of http://stackoverflow.com/questions/847066/group-by-multiple-columns — auburg, Sep 30 '16 at 11:39

score 3 · Accepted Answer · answered Sep 30 '16 at 12:11

Given this class that represent your database table mapping

public class DatabaseAnimal {
    public int id { get; set; } 
    public string animal { get; set; }
    public string type { get; set; }
}

And these classes to represent the projection you want to do

public class Animal {
    public Animal() {
        animalType = new List<AnimalType>();
    }

    public string animalDesc { get; set; }
    public List<AnimalType> animalType { get; set; }
}

public class AnimalType {
    public string typeDesc { get; set; }    
}

You can project (using Select) your group into your model

var result = dbAnimal.GroupBy(a => a.animal)
                     .Select(grp => new Animal {
                         animalDesc = grp.Key,
                         animalType = grp.Select(e => new AnimalType {
                             typeDesc = e.type 
                         }).ToList()
                      });

.NET Fiddle

Also, I highly suggest you adopt C# Capitalization Convention as a naming convention in your classes.

Group By + Select into New Nested Class Structure

1 Answers1