National ranking issues (LINQ table Query equals SQL LEFT join)

Preface: To get the national ranking, (because of the permissions problem, the data displayed is not the national data, but a partition of the data, therefore, can not get the data sorted by ranking)

Show employee points in this part and get a nationwide ranking.

My train of thought: Get the employee information displayed in collection 1, get the national employee ID, points and, rank data set 2, according to the collection 1 employees in set 2 directly to the ranking out

1, get the displayed employee information List1 (not show it specifically)

2, get the national employee ID, points and, ranked data set 2

Table 1: Employee table 2: Credit Table

An employee may have multiple points records, or no data

1   varNationalpaiming = ( fromUinchdbcontext.users2Join IRinch( fromIinchdbcontext.integrals3                                               wherei.isdeleted = =false4 Group I by I.userid into G5                                               Select New6                                               {7UserID =G.key,8                                                  Totalscore = g.sum (E = E.integralvalue) //The sum of the employees ' points   9 }) on U.userid equals IR. UserIDTen Into GC One                                    From GCI in GC. DefaultIfEmpty ()//Zolian equivalent to SQL    A                                    where(Deptsystem = =0?true: D.deptsystem = = Deptsystem) && (state = =1? u.isdeleted = =false: u.isdeleted = =true) //may not have an employee's score record in the table  -                                    Select New -                                    { theUserId =U.userid, -Totalscoure = gci==NULL?0: GCI. Totalscore, -                                    } -).orderbydescending (e=>e.totalscoure). ToList ();

Convert to ViewModel (calculates the rank of each person)

1list<userrankingviewmodel> list =NewList<userrankingviewmodel>();2              for(inti =0; i < Nationalpaiming.count; i++)3             {4Userrankingviewmodel model =NewUserrankingviewmodel ();5Model. UserID =Nationalpaiming[i]. UserId;6Model. Totalintergal =nationalpaiming[i].totalscoure;7                 if(i = =0) //Because the collection of loops here is arranged in descending order of total integrals, so the first ranking must be the first 8                 {9Model. Ranking = i +1;Ten                 } One                 Else A                 {
Determine if the overall score of the employee is the same as the total score for the previous employee -                     if(Nationalpaiming[i].totalscoure = = Nationalpaiming[i-1].totalscoure) -                     { theModel. Ranking = List[i-1].  Ranking; //Same rank same  -                     } -                     Else -                     { +Model. Ranking = List[i-1]. Ranking +1; //Different is the next member of the previous employee  -                     } +                 } A list. ADD (model); at}

3. Loop Collection 1 Entity conversions when returning a page

1  list<int> nationaluserids = nationallist.select (e = e.userid). ToList (); 2                 int index = userids.indexof (viewmodel.userid); 3                 viewmodel.nationalranking = Nationallist[index]. Ranking;

Note: Only personal thinking and implementation of notes, inappropriate (or have a better way), please correct me.

National ranking issues (LINQ table Query equals SQL LEFT join)