I have a table of Users and a one to many UserSkills table. I need to be able to search for users based on skills. This query takes a list of desired skills and searches for users who have those skills. I want to sort the users based on the number of desired skills they posses. So if a users only has 1 of 3 desired skills he will be further down the list than the user who has 3 of 3 desired skills.

I start with my comma separated list of skill IDs that are being searched for:

List<short> searchedSkillsRaw = skills.Value.Split(',').Select(i => short.Parse(i)).ToList();

I then filter out only the types of users that are searchable:

List<User> users = (from u in db.Users
                    where
                        u.Verified == true &&
                        u.Level > 0 &&
                        u.Type == 1 &&
                        (u.UserDetail.City == city.SelectedValue || u.UserDetail.City == null)
                    select u).ToList();

and then comes the crazy part:

var fUsers = from u in users
             select new
             {
                 u.Id,
                 u.FirstName,
                 u.LastName,
                 u.UserName,
                 UserPhone = u.UserDetail.Phone,
                 UserSkills = (from uskills in u.UserSkills
                               join skillsJoin in configSkills on uskills.SkillId equals skillsJoin.ValueIdInt into tempSkills
                               from skillsJoin in tempSkills.DefaultIfEmpty()
                               where uskills.UserId == u.Id
                               select new
                               {
                                   SkillId = uskills.SkillId,
                                   SkillName = skillsJoin.Name,
                                   SkillNameFound = searchedSkillsRaw.Contains(uskills.SkillId)
                               }),
                 UserSkillsFound = (from uskills in u.UserSkills
                                    where uskills.UserId == u.Id && searchedSkillsRaw.Contains(uskills.SkillId)
                                    select uskills.UserId).Count()
             } into userResults
             where userResults.UserSkillsFound > 0
             orderby userResults.UserSkillsFound descending
             select userResults;

and this works! But it seems super bloated and inefficient to me. Especially the secondary part that counts the number of skills found.

Thanks for any advice you can give.

--r