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 skill 1,2 )
2 ( Since he/she matches skill 1,2 )
3 ( Since he/she matches skill 3 )
4 ( Since he/she matches skill 3 )

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

2 ( Since he/she matches Required skill 1,2 and have additional one skill, so priority is more than StudentID 1 )
1 ( Since he/she matches Required skill 1,2 )
4 ( Since he/she matches Required skill 3 and have additional three skill, so priority is more than StudentID 3 )
3 ( Since he/she matches Required 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.

CodePudding user response：

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();