c# - Entity Framework : How to groupjoin? (Query users within location with additional requirements) -

scenario:

a user can assign himself multiple categories (ex. "fishing", "stackoverflowfan", etc..)
user wants befriend(connection) similar user b.
there plenty of similar user b's, user wants narrow down based on location & activity of user b.
user doesn't want or befriended or blocked.

how implement in entity framework (core)?
i've got work finding based on location , activity date , think filtering on connections(befriending) , blocked users, filtering on categories got me scratching head bit.

the result picture in head:
if there x-amount of users in system should get:
1) closest users in x-radius (ex. 10 km/miles)
2) based on closest users, order based on similar categories
3) cool, have users similar categories, let's see of users online, categories more important , maybe order closest location? or much?

userid | location | activitydate | categories
----------------------------------------------------------------
21 | point( lon lat) within radius | 2017-26-01 | {1,2,12,3,5} (5 common categories)
12 | point( lon lat) within radius | 2017-26-06 | {1,2,12} (3 common categories)
13 | point( lon lat) within radius | 2017-26-07 | {1,2} (2 common categories)
44 | point( lon lat) within radius | 2017-26-07 | {} (0 common categories)
25 | point( lon lat) within radius | 2017-26-06 | {} (0 common categories)
33 | point( lon lat) within radius | 2017-26-05 | {} (0 common categories)

extra: while on streak, want activitydate (so last login) not ordered example maximum 2 weeks old, because no use in befriending user hasn't been online in decades, no matter how many similar categories have.

here simple example of models:

user model:

public class user {     public int id { get; set; }     public datetime activitydate { get; set; }      public location location { get; set; }     //user can have multiple connections     public icollection<connection> connections { get; set; }     //user can have multiple categories     public icollection<usercategory> usercategory { get; set; } } 

category model:

public class category {     public int id { get; set; }     public string name { get; set; }     //category can have multiple users     public icollection<usercategory> usercategory { get; set; } } 

intersection table usercategory (manytomany - user categories):

public class usercategory {     public int userid { get; set; }     public user user { get; set; }      public int categoryid { get; set; }     public category category { get; set; } } 

intersection table connection (manytomany - user user):

public class connection {     public int userid { get; set; }     public user user { get; set; }      public int userconnectionid { get; set; }     public user userconnection { get; set; }      public bool isblocked { get; set; } } 

this have far:

return await mydbcontext.user.asnotracking().fromsql(         @"declare @from geography = geography::stpointfromtext([location])', 4326);             declare @to geography = geography::stpointfromtext('point(' + cast({0} varchar(20)) +' ' + cast({1} varchar(20)) +')', 4326);         select *         dbo.user         @from.stdistance(@to) <= @p0",         requestor.location.longitude,         requestor.location.latitude,         requestor.appsetting.radius)         .where(x => !x.connections.any(y => y.userid == requestor.id))         .groupjoin(/*do use groupjoin?...*/).orderby(/*...*/).thenby(x => x.activitydate) //<=========         .skip(skip)         .take(take)         .tolistasync();  

p.s: i'm working in entity framework core (so dbgeography out of question)

any push in right direction appriciated!

thank you!

you don't need groupjoin because can use existing navigation properties let ef generate required joins in sql. gist of query user's categories intersected user's categories:

var users = mydbcontext.user                        .asnotracking().fromsql(...)                        .where(x => !x.connections.any(y => y.userid == requestor.id)); var result = (from u1 in users              u2 in u1.connections.select(c => c.userconnection)              let commoncategories = u1.usercategory.select(uc1 => uc1.categoryid)                  .intersect(u2.usercategory.select(uc2 => uc2.categoryid))              orderby commoncategories.count(), u1.activitydate              select new              {                  u2.id,                  u2.location,                  u2.activitydate,                  commoncategories = commoncategories.select(cc => cc.id)              })              .asenumerable()              .select(x => new              {                  x.id,                  x.location,                  x.activitydate,                  categories = string.join(",", x.commoncategories)              }); 

the asenumerable() statement put in-between continue query execution linq-to-objects, because linq-to-entities doesn't support string.join.

note: i'm not sure if you'd want first user's id in there too. query produces list of connected users per user in users, without first user's id can't tell user each row belongs. it's not necessary if execute query 1 user.