Many to many ordered by count of relationship (Entity Framework, Linq)

Question

i have a table structure like this...

When I import this into entity framework it looks like this...

What I need to do is build a query from LINQ that will return a list of every unique store, populated with a list of people who like that store. (easy, right?)

THE CATCH: I need to filter the list to the person's list of friends that get passed in as a List to the linq query (comes from facebook, so relationships are not in db)...

ONE MORE THING: i need to return if the store is a favorite of the person requesting the data (the uid as shown below)

OK, ANOTHER THING: I need to return the list sorted by the highest number of friends who like an item to lowest (the ui below is wrong in that regard)

Here is the method signature of the linq query i need

public List<Store> GetTopStoresFilteredByFriends
          (int uid, List<int> friends, int size = 10){

}

To return a user interface that looks like this...

Answer 1

This query gives you a list of every unique store, populated with a list of people who like that store which is also liked by at least one of the friends in the list that you are passing order by the highest number of friends that like the store plus a flag to show whether or not each store is liked by the person who requested the data:

var query = (from s in ctx.Stores.Include("People")
             from p in s.People
             let n = friends.Sum(f => s.People.Count(item => item.PersonID == f))
             where friends.Any(f => f == p.PersonID)
             select new {
                 TheStore = s,
                 IsFavorite = s.People.Any(ppl => ppl.PersonID == uid),
                 N = n
             })
             .Distinct()
             .OrderByDescending(o => o.N);

Answer 2

pseudo code

var stores = new Dictionary();

//one entry for each store, starting with count = 0

stores.Add(1, 0);

stores.Add(2, 0);

stores.Add(3, 0);

var peopleWithFriends = new Dictionary < int, List < Person > > ();

//one entry for each person containing the list of friends
var person0 = new Person(); person0.Id = 0;

var person1 = new Person(); person1.Id = 1;

var person2 = new Person(); person2.Id = 2;

peopleWithFriends.Add(0, new List < Person > {person1, person2}); peopleWithFriends.Add(1, new List < Person > { person0, person2 }); peopleWithFriends.Add(2, new List< Person > { person0, person1});

foreach (var store in peopleWithFriends.Keys.Select(personId => peopleWithFriends[personId]) .SelectMany(friends => Enumerable.SelectMany(friends, friend => friend.Stores))) { stores[store.Id]++; }

now you can sort the stores dictionary by the highest count, and return the result