CODEWITHSUNDEEP
c#linq-to-entities
Maelig
Maelig

Reputation: 2096

Get children count in linq to entities query

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

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.nbSounds = 18

Upvotes: 2

Views: 4699

Answers (3)

Flater
Flater

Reputation: 13823

The answer lies in the user of the SelectMany operator.

int count_soundsinparent = parentCategory
    .Select(x=> x.ChildCategory)
    .SelectMany(x=> x.Sounds)
    .Count();

Suppose you would've used a regular Select; it would return you a list (one item per child category) of a list of sounds (one item per sound the child category has), thus creating a two-dimensional list.

What SelectMany does is take all the elements of that list, and put it in a one dimensional list. Then you count the items in that list and voila, you have the total.

Upvotes: 4

Maelig
Maelig

Reputation: 2096

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 !

Upvotes: 0

AakashM
AakashM

Reputation: 63378

recursively

This requirement means you're not going to do it in a single LINQ query. Linq can't really express a recursive query with linq to objects, let alone a more comlex provider such as Linq to Entities.

With the schema you describe, you'd be better off writing a stored procedure. If you had a nested sets model, you might be able to do some mathematical manipulation of left edge and right edge numbers, but probably not with those extra rules.

Upvotes: 1

Related Questions