0

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.

Improve this question
1
  • Please provide me... Stack Overflow rule is: try first, then ask. Recursive querying with LINQ is a recurring question at Stack Overflow. If you look for recursive (or hierarchical) queries + LINQ you will find many answers. Well, actually, lack of answers because LINQ + recursive is not a good fit. Also, when you tag [linq] it is assumed you're targeting linq-to-objects, which you clearly don't. Knowing the type of LINQ is always important. As is the database you're targeting. Commented Nov 9, 2013 at 22:10

1 Answer 1

Reset to default
0

Try below code

Declare @table table
(ID int,
Name varchar(20),
SponsorID int,
PairSide varchar(10)
)
insert into @table(ID,Name,SponsorID,PairSide)
values(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')
Select Sponsorid,
Count(SponsorID) TotalChild ,
Count((Case when PairSide = 'Left' Then 1 end)) LeftChild ,
Count((Case when PairSide = 'Right' Then 1 end)) RightChild
from @table
Group by SponsorID

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

it is good but it is giving output of only one level i.e the childs of 1001 or childs of 1002. i want to get subchilds of 1001 also i.e child of 1002 as 1002 is a child of 1001

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.