How to get the grouped result with an EF query for the following data

Question

I have a table Person with firstname, last name, id. Another table with roles(string) for that person, another table with categories(string). these roles and categories are connected with Occupation table (it consists of relation between roles and categories with their foreign key reference), base table PersonOccupation consists of relation between Person and Occupation table

i need to search whether firstname or last name or roles or categories consist of a letter :"A", the resultant should be

{
    "Res": [
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Categories": [
                "HumanResource",
                "It professional"
            ],
            "Roles": [
                "Manager",
                "Social Activist"
            ]
        },
        {
            "PersonId": "2",
            "LastName": "Tex",
            "FirstName": "Roy",
            "Categories": [
                "HumanResource",
                "It professional"
            ],
            "Roles": [
                "Manager",
                "Social Activist"
            ]
        }
    ]
}

The LINQ I used is

Context.PersonOccupation
    .Include(x => x.Occupation)
    .ThenInclude(x => x.categories)
    .Include(x => x.Occupation)
    .ThenInclude(x => x.roles)
    .Include(x => x.Person)
    .AsNoTracking();

What i get is

{
    "Res": [
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Categories": "HumanResource"
        },
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Categories": "It professional"
        },
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Roles": "Manager"
        },
        {
            "PersonId": "1",
            "LastName": "Chris",
            "FirstName": "Angel",
            "Roles": "Social Activist"
        }
    ]
}

Answer 1

if I understand correctly, your three classes should be:

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public IList<Role> Roles { get; set; }
    public IList<Category> Categories { get; set; }
}

public class Role
{ 
    public int RoleId { get; set;}
    public string Name {  get; set; }            
    public IList<Person> Persons { get; set; }    
}

public class Category
{
    public int CategoryId { get; set;}
    public string Name { get; set; }            
    public IList<Person> Persons { get; set; }
}

and your code, without thinking too much, could be:

List<Person> searchPersons = db.Persons.Where(x => x.FirstName.Contains("A") || x.LastName.Contains("A")).ToList();
List<Person> searchRoles = db.Persons.Include(x => x.Roles).Where(x => x.Roles.Any(x => x.Name.Contains("A"))).ToList();
List<Person> searchCategories = db.Persons.Include(x => x.Categories).Where(x => x.Categories.Any(x => x.Name.Contains("A"))).ToList();
searchPersons.AddRange(searchRoles);
searchPersons.AddRange(searchCategories);

being the final result put in searchPersons.