Search code examples
c#linq-to-entities

Get children count in linq to entities query

I have a many to many relation between two tables in my database (MySql) :

  • Category (id, name, id_parent)
  • Sound (id, name)

A category can have 1 parent category, so a category can have many children. If a category has at least one child, it has no sounds in it. I do not allow sounds and children categories together.

So for a parent category, i want to get all children that may have children that may have children (...) and i want to count sounds in all children recursively.

Exemple :

Cat -> (CCat1 -> CCCat11(2 sounds), CCat2 (5 sounds), CCat3 -> CCat31 -> CCCat311 -> (CCCCat3111 (10 sounds), CCCCat3111 (1 sound))

There is no deep limit, that's why i need a "recursively" way to count sounds.

  • Cat has 3 children.
  • CCat1 has 1 child.
  • CCat2 has no child.
  • CCat3 has 1 child that has 1 child that has 2 children.

Cat.nbSounds = 18

Solution

  • After more researches it seems that we can't do what i want.

    So i created a recursive method that retrieve children and calculate the number of sounds in each category (i count sounds in the category or i count categories children number of sounds).

    If you find a magical way to do it anyway with linq to entities, i take it !