Im currently using LinqToSql to get a list of groups from the database. (tblGroups)
In code, im iterating through my collection (tblGroups) and counting sub-elements of tblGroups and storing the whole object in a DTO (simple object) with an additional property (UserCount).
When these are stored in a simple object, the UserCount is worked out by looking through each group and counting the users inside and making sure they fit a certain role.
This starts to hamper performance when I have so many groups and i would like to take all of this to the database layer and work it all out initially.
My question is:
What is the best way to achieve this? I would ideally like to remove the DTO as this adds a whole new layer. Could I add a new property to the database (like a calculated field) that is populated everytime this info is request as say: a stored procedure?
View Complete Post