Finding Overlapping Interests in LINQ

Question

I have a model where a place has some descriptions, those descriptions are associated with interests (place.description.interests). A user looking at the view for a place is represented in the model as a user, who also has a number of interests.

What I want to do is sort the description by overlapping interests (including zero overlapping), where my current Linq is:

place dest = (from p in _db.places
                          where p.short_name == id
                          select p).Single();

 return View(dest);

Now the following will do what I want in SQL on the schema in question:

SELECT COUNT(interest_user.user_id) AS matches, description.*
FROM description JOIN interest_description ON description.user_id = interest_description.user_id AND description.place_id = interest_description.place_id
    JOIN interest ON interest_description.interest_id = interest.interest_id
    LEFT JOIN interest_user ON interest.interest_id = interest_user.interest_id 
WHERE interest_user.user_id = 2
    AND description.place_id = 1
GROUP BY interest_description.user_id, interest_description.place_id
ORDER BY matches DESC

But I'm too new to Linq to know how I would handle this correctly. Ideally I could pull this off while still passing in a strongly typed model.

I have managed this so far:

var desc = from d in _db.descriptions
                       from i in d.interests
                       from u in i.users.DefaultIfEmpty()
                       where d.place_id == PlaceID
                           && (u.user_id == userID

(PlaceID and UserID are arguments passed to the controller that is managing this).

Simply put, given this linq, I just need to return d, ordered by a count of i.

my model

Answer 1

when your linq query becomes overly complex, i will suggest you creating views in your database and putting them on dbml designer. I have experienced at couple of situations when doing a lot of grouping in linq queries resulted in inefficient sql. Using views will not only result in straight forward linq queries but it also uses the sql that you want.

Answer 2

place current_place =
    _db.places
    .Include("descriptions.interests.users")
    .Where(p => p.place_id == place_id)
    .First();

var interesting_descriptions =
    from description1 in current_place.descriptions
    select new {
        description = description1,
        matches = (
            from interest1 in description1.interests
            from user1 in interest1.users
            where user1.user_id = user_id
            select 1
        ).Count()
    } into result
    orderby result.matches descending
    select result;

This is roughly equivalent to the SQL

SELECT
    description.*,
    (
        SELECT COUNT(*)
        FROM interest_description
        INNER JOIN interest_user
            ON interest_user.interest_id = interest_description.interest_id
        WHERE interest_description.place_id = description.place_id
        AND interest_description.user_id = description.user_id
        AND interest_user.user_id = @user_id
    ) AS matches
FROM description
WHERE place_id = @place_id
ORDER BY matches DESC

For each description associated with the given place, it counts the number of times the given user occurs on any associated interest.

It will give matches = 0 for descriptions that does not have any common interests with the user.

Since GROUP BY/group ... by ... into have a hard time dealing with empty sets with conditions, it is necessary to use an inner query.