Search for overlapping interests in LINQ

Question

I have a model where the place has some descriptions, these descriptions are related to interests (place.description.interests). A user looking at a representation of 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 overlap), where is my current Linq:

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 find out how I will handle this correctly. Ideally, I could pull this out while still passing in a strongly typed model.

I succeeded:

 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 the arguments passed to the controller that controls this).

Simply put, given this linq, I just need to return the d ordered by account i.

my model

Answer 1

when your linq query becomes overly complex, I suggest you create views in your database and put them in the dbml constructor. I came across two situations where many groups in linq queries led to sql inefficiencies. Using views will result not only in direct linq queries, but also in the sql used.

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 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 a given place, it counts the number of times a given user appears for any related interest.

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

Since GROUP BY / group ... by ... into can hardly handle empty sets with conditions, you must use an internal query.