shana0721shana shana0721shana - 6 days ago 5
C# Question

LINQ select out list of values and map into one field property in the nested class structure

Animal:

+----------+---------+--------+
| animalId | animal | typeId |
+----------+---------+--------+
| 1 | snake | 1 |
| 2 | cat | 2 |
+----------+---------+--------+


AnimalType:

+--------+----------+
| typeId | type |
+--------+----------+
| 1 | reptile |
| 2 | mammal |
+--------+----------+


AnimalBody:

+--------+-------+----------+
| bodyId | body | animalId |
+--------+-------+----------+
| 1 | tail | 1 |
| 2 | eye | 1 |
| 3 | tail | 2 |
| 4 | eye | 2 |
| 5 | leg | 2 |
+--------+-------+----------+


Table relation:


  • Animal.typeId = AnimalType.typeId

  • Animal.animalId = AnimalBody.animalId




I need to output them into JSON format as below:

{
animalId: 1,
animal: "snake",
type: "reptile",
body: {
"tail", "eye"
}
},
{
animalId: 2,
animal: "cat",
type: "mammal",
body: {
"tail", "eye", "leg"
}
}


How can I achieve this with pure LINQ clauses instead of method?

I have tried:

from animal in db.Animal
join animalType in db.AnimalType on animal.typeId equals animalType.typeId
select new
{
animalId = animal.animalId,
animal = animal.animal,
type = animalType.type,
body = ?
};

Answer

Assuming you want the body element to be an array of body parts, here's what you should do: Join Animals with AnimalTypes:

var animalsWithType = db.Animals.Join(
    animal => animal.typeId,
    animalType => animalType.typeId,
    (animal, type) => new { animal, type });

Afterwards, GroupJoin animalsWithType with AnimalBody elements:

var result = animalsWithType.GroupJoin(db.AnimalBodies,
    animalWithType => animalWithType.animal.animalId,
    body => body.animalId,
    (animalWithType, bodyParts) => new
    {
        animalId = animalWithType.animal.animalId,
        animal = animalWithType.animal.animal,
        type = animalWithType.type.type,
        body = bodyParts.Select(part => part.body)
    });

Now, just export the result to JSON and you should be set.