How do I convert this SQL to Linq?

Question

What would this SQL look like in Linq?

You can see the SQL statement that I want to use, I am pretty new to Linq:

SELECT user_id
FROM profiles
LEFT JOIN interests ON profiles.id = interests.profile_id
WHERE interest IN ('Shopping', 'Art') 
  AND Sex IN ('Man', 'Woman') 
  AND user_id NOT IN (SELECT user_1 FROM matches) 
  AND user_id != 84
GROUP BY user_id
ORDER BY COUNT(user_id) DESC;

Beneath you can see the different models, All the models have an id from modelbase

Profile model:

namespace Sparks.Presentation.Entities
{
    public partial class Profile: ModelBase
    {
        public Profile()
        {
            Interests = new HashSet<Interest>();
        }

        public int UserId { get; set; }
        public string Name { get; set; }
        public string Picture { get; set; }
        public string Birthdate { get; set; }
        public string Sex { get; set; }
        public string Orientation { get; set; }

        public virtual User User { get; set; }
        public virtual ICollection<Interest> Interests { get; set; }

        [NotMapped]
        public IFormFile FormFile { set; get; }
    }
}

Interest model:

namespace Sparks.Presentation.Entities
{
    public partial class Interest: ModelBase
    {
        public int ProfileId { get; set; }
        public string InterestName { get; set; }

        public virtual Profile Profile { get; set; }
    }
}

Matchmodel:

namespace Sparks.Presentation.Entities
{
    public partial class Match: ModelBase
    {
        public Match()
        {
            Chats = new HashSet<Chat>();
        }

        public int User1 { get; set; }
        public int User2 { get; set; }
        public bool Matchstatus { get; set; }
        public bool Matchmade { get; set; }
        public DateTime CreatedOn { get; set; }

        public virtual User User1Navigation { get; set; }
        public virtual User User2Navigation { get; set; }
        public virtual ICollection<Chat> Chats { get; set; }
    }
}

Hope someone can help me out?

Answer 1

This is closer to the query what is expected. Looks similar as SQL but select part is last statement.

var interestFilter = new [] {"Shopping", "Art"};
var sexFilter = new [] {"Man", "Woman"};

var query = 
  from p in ctx.Profiles
  from i in p.Interests
  where interestFilter.Contains(i.InterestName) && sexFilter.Contains(p.Sex)
    && !ctx.Matches.Any(m => m.User1 == u.UserId)
    && p.UserId != 84
  group p by p.UserId into g
  order by g.Count()
  select g.Key;

var result = query.ToList();