CODEWITHSUNDEEP
linqlinq-to-entities
Niels Brinch
Niels Brinch

Reputation: 3612

LINQ group by parent and parent's parents

I have two tables:

Work

Employee

EmployeeBossId refers to another Employee, who may also have a boss and so on.

The owners can be in an infinite number of hierarchies (although in reality, it's just in 3 levels). I'm looking for a clever database call in the shape of a LINQ statement, that can receive an EmployeeId and tell me the sum amount of ALL hours for that employee, including any 'sub' employees - grouped by each immediate sub-employee!

Example

If I provide the EmployeeId of Jim I want the total hours for Joey, Anthony (including hours for Ben and Rita) and for Sid.

I know I'm asking a bit much, but I am having trouble deciding how to attack this one and hoped it was clear to someone else out there. Thanks.

I'm looking for the entire thing to be done in ONE database call.

Upvotes: 1

Views: 85

Answers (1)

Maarten
Maarten

Reputation: 22945

SQL Server supports something called Common Table Expressions, which basically allows you to do 'recursive queries', which is just what you need to solve the parent/parent-parent thing.

This unfortunately is not supported by Linq. But there is a way to do it, but it requires some SQL in your C# code (in the context to be precise). See this question and this howto.

Upvotes: 1

Related Questions