LINQ to SQL .Count takes way to much process time and decreases performances.

Question

LINQ to SQL .Count takes way to much process time and decreases performances.

I am doing a recursive loop and for one child (lets call it parent) I have to check the number of children under it to make a decision if it should be included or not.

The Count is too slow 8 ms :( for 120 parent records.

Any ideas to make it quicker.

Answer 1

You could select a projection from your database getting the parent and the count of child element it has. This avoids round tripping to the database.

var query = from x in DataContext.Parents 
            select new {Parent = x, Count = x.Childs.Count() };

Now loop over the results and to whatever you want to next.

If you just want to filter (=where clause) based on the child element count, do it like this:

var query = from x in DataContext.Parents 
            where c.Childs.Count() > 10
            select x;

Linq to SQL will try to translate your calls to IEnumerable.Count() to a SELECT COUNT(*), which should be pretty performant.

Answer 2

To me it sounds like you are looping over a result set, doing another Linq-to-sql query for each result. In that case it is bound to be slow since you will be doing a lot of extra database roundtrips.

You will have to rewrite your question to get the children count together right away in the first query.