### Description

I have three tables.

**STUDENTS**

ID Name 1 AAA 2 BBB 3 CCC 4 DDD

**Skills**

ID Name 1 JS 2 C# 3 Angular 4 React 5 jQuery 6 TS 7 F#

**StudentSkill**

ID StudentID SkillID 1 1 1 2 1 2 3 2 1 4 2 2 5 2 4 6 3 3 7 4 3 8 4 5 9 4 6 10 4 7

From this table I’m trying to get students details based on the course (Skill) they enrolled in.

Suppose Required course is **1, 2, 3** then the result should be.

- Student with ID
**1, 2, 3, 4**

1

( Since he/she matches skill1,2)

2( Since he/she matches skill1,2)

3( Since he/she matches skill3)

4( Since he/she matches skill3)

- Ordering of the Students should be
**2, 1, 4, 3**.

2

( Since he/she matchesRequired skill 1,2and have additional one skill, so

priority is more thanStudentID 1)

1( Since he/she matchesRequired skill 1,2)

4( Since he/she matchesRequired skill 3and have additional three skill, so priority is more thanStudentID 3)

3( Since he/she matchesRequired skill 3)

#### What I have tried

```
var requiredSkillIds = new List<int>() { 1, 2, 3 }; // Required Skills.
var studentIds= (await _studentSkill.GetByAsync(x => requiredSkillIds.Contains(x.skillId))).Select(x => x.StudentID).ToList();
var result = await _dbContext.Students.Where(x => studentIds.Contains(x.Id).Include(x =>x.StudentSkills).OrderByDescending(x =>x.StudentSkills.Count).ToListAsync();
```

The problem is that I’m getting result, but sorting is done on the total Skill count of a student.

So, the result I’m getting after sorting is students with ID.

4, 2, 1, 3

How can I achieve the sorting as I mentioned above.

### >Solution :

You’re only counting the amount of skills the student has, but not taking into account how many required skills they have. You may need to use linq to objects but add this clause:

```
var result = await _dbContext.Students.Include(s => s.StudentSkills)
.Where(s => studentIds.Contains(s.Id))
.OrderByDescending(s => s.StudentSkills.Count(k => requiredSkillIds.Contains(k.SkillId)))
.ThenByDescending(s => s.StudentSkills.Count);
.ToListAsync();
```