GET Values from table which are not associated directly

See more:

SQL

LINQ

I am trying to work with LINQ and i am stuck here. My problem is i want to retrieve the count of all the users in a specific side. Let me Explain. Here is my table snapshot with sample data.

ID Name SponsorID PairSide
1001 User1 1000 Left
1002 User2 1001 Left
1003 User3 1001 Right
1004 User4 1001 Right
1005 User5 1004 Left
1006 User6 1004 Right
1007 User7 1002 Left
1008 User8 1002 Left
1009 User9 1003 Right
1010 User10 1001 Right



Now the data is like .. User1 has 4 childs , 1 in left side and 3 in right side , User2 has two childs both in left and so on.
Since user2 is a child of User1 so the childs of User2 will also become child of User1 . So according to above data .User1 have a total of 4 childs in left and 5 childs in right.Aplogies if any mistakes. but i want some linq or sql code to calculate these things

1. Total count of users in left of a particular ID
2. Total count of users in right of a particular ID
3. Enumeration of Child Users so that i can use it to show a tree

This is dummy data and my actual website will contain at least 20,000 of data and i have to do calculation there. Please provide me logic for this.