Querying distinct columns across rows filtering by another column

Question

I'm working on a project using Linq and Entity Framework Core.

I'm struggling with a query.

I have two data access models.

public class JobPost
{
  public int Id {get;set;}
  public string RoleTitle  { get; set;}
  public Domain Domain1 {get;set;}
  public Domain Domian2 {get;set;}
  public Domain Domain3 {get;set;}
  public Domain Domain4 {get;set;}
  public Domain Domain5 {get;set;}
}

[Owned]
public  class Domain
{
   public int? CategoryId {get;set;}
   public int? SubcategoryId {get;set;}
}

A job post is an entity/table with Id as key. It has one role title(mandatory) and 1-5 domains(this explains why Id properties of Domain is nullable). Domain has CategoryId and SubcategoryId as keys. Job seekers will be able to filter job posts by domains. Examples on domains are:

            Category      Subcategory
Domain      IT            Mobile
Domain      IT            Database
Domain      IT            Design
Domain      Transport     Driving
Domain      Transport     Spedition

Two domains are distinct if they differ by their categoryId OR subcategoryId.

User case for the query in question: User(Employer X) has posted 3 job posts: Y ,Z and W. Both Y and Z have "Sotware developer" as role title while W has"Front-end developer". Job post Y has one domain: IT,Mobile. Job post Z has two domains: IT,Mobile and IT,Database. Job post W has one domain: IT,Design. User now wants to create a third post. User clicks on the +Post button and is prompted to either select a previous role title("Sotware developer" or "Front-end developer") or create a new one. In this case, user selects "Sotware developer" from the previous-item list. Next, user is prompted to submit domains. We want to make it easy for our user by presenting a selectable list of distinct domains that has been previously applied for job posts with role title "Software developer". In this specific case there are two domains on the list, "IT,Mobile" and "IT,Database", which the user can apply.

So the question is how do we write this query in Linq so we get an IEnumerable of all distinct domains that are applied for a specific role title across all job posts, performed by SQL server and NOT by the client.

I've tried different strategies such as projectiong, joining, grouping in Linq but without the results I want. The only way I can do this is to retrieve all job posts filtered by role title and remove duplicates in my viewmodel.

from p in dbCotext.JobPosts
where p.RoleTitle == roleTitle
select p

//client code for removing duplicates, not what I want

Is it even possible to do this in Linq or do I have to write this query in SQL?

Thanks in advance.

Answer 1

 PreviousSubcategories =
          from c in dbContext.Categories
             let posts = 
             from p in a.Posts p in dbContext.Posts
             where p.RoleTitle == roleTitle
             where (c.Id == p.Domain1.CategoryId
             || c.Id == p.Domain2.CategoryId
             || c.Id == p.Domain3.CategoryId
             || c.Id == p.Domain4.CategoryId
             || c.Id == p.Domain5.CategoryId
             orderby p.CreatedOn descending 
             select new Domain[]
                   {
                     p.Domain1,
                     p.Domain2,
                     p.Domain3,
                     p.Domain4,
                     p.Domain5
                   }
            where posts.Any()
            select new DomainViewModelDto
                  {
                      SubcategoryId = (int)c.Id,
                      SubcategoryName = c.Name,
                      LatestExperienceLevelsApplied = new LatestExperienceLevelsApplied
                      {
                          IsExperienceLevel1Applied = (bool)posts.First().Where(d => d.CategoryId == c.Id).First().IsExperienceLevel1Applied,
                          IsExperienceLevel2Applied = (bool)posts.First().Where(d => d.CategoryId == c.Id).First().IsExperienceLevel2Applied,
                          IsExperienceLevel3Applied = (bool)posts.First().Where(d => d.CategoryId == c.Id).First().IsExperienceLevel3Applied,
                          IsExperienceLevel4Applied = (bool)posts.First().Where(d => d.CategoryId == c.Id).First().IsExperienceLevel4Applied,
                          IsExperienceLevel5Applied = (bool)posts.First().Where(d => d.CategoryId == c.Id).First().IsExperienceLevel5Applied,
                          IsExperienceLevel6Applied = (bool)posts.First().Where(d => d.CategoryId == c.Id).First().IsExperienceLevel6Applied,
                      }


                  }