is there a better way to write this frankenstein LINQ query that searches for values in a child table and orders them by relevance?

Question

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

Answer 1

Why not just let people do, say, fUsers.UserSkills.Count()? It would reduce the amount of data retrieved from the server in the first place.

Alternatively, you could create a View that has a calculated field in it and then map that to a type. Would push the query for count down into the DB.

Answer 2

Hi,

I think that should do the trick:

(from u in users
where u.UserSkills.Any(skill => searchedSkillsRaw.Contains(skill.SkillId))
select new
{
    u.Id,
    u.FirstName,
    u.LastName,
    u.UserName,
    UserPhone = u.UserDetail.Phone,
    UserSkills = u.UserSkills,
    UserSkillsFound = u.UserSkills.Where(skill => searchedSkillsRaw.Contains(skill.SkillId)).Count()
} into userResults
orderby userResults.UserSkillsFound descending
select userResult).ToList();

However, since this is a query that gets executed on SQL server I strongly recommend to remove the 'ToList()' call from the first query. Because that actually causes LINQ to run two separate queries on the SQL server. You should change it to IQueryable instead. The power of LINQ is to construct queries in several steps without having to actually execute it in between. So 'ToList' should be called only at the end when the entire query has been constructed. In fact what you currently do is running the second query in memory rather than on the database server.

In regards to your UserSkills one-to-many relation you do not need to do an explicity join in LINQ. You can just access the collection property instead.

Let me know if you need more explanation.

Michael