How can I achieve the Ordering result as I mentioned below in C# Entityframe-work?

useroiJc1 0
Viewed 61

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.

sql-order-by linq entity-framework .net c#
1 Answers

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();
Jeff Mercado0
Related