How to rewrite a SQL join from two tables with a join in LINQ?

Question

I have 3 classes as below:

public class A
{
    public string Id {get; set;}
    public string Name {get; set;}
    public string Age {get; set;}
    public bool IsEmployed {get; set;}
    public int RoleId {get; set;}
}

public class B
{
    public string Id {get; set;}
    public string Name {get; set;}
    public string Age {get; set;}
    public int RoleId {get; set;}
}

public class Roles
{
    public int Id {get; set;}
    public string RoleName {get; set;}
    ...
}

Suppose these classes have their own tables in the DBMS.

I currently have an SQL query that I would like to rewrite in LINQ (as wide as possible)

SELECT A.Name, A.Age, Roles.RoleName, A.IsEmployed 
FROM A 
JOIN Roles ON A.RoleId = Roles.Id
WHERE Roles.RoleName = 'ADMIN'

UNION

SELECT B.Name, B.Age, Roles.RoleName, '-' as IsEmployed 
FROM B 
JOIN Roles ON B.RoleId = Roles.Id
WHERE Roles.RoleName = 'ADMIN'

Currently, I have managed to rewrite it as:

var filteredClassA = from c in allClassAs
    join role in allRoles on role.Id equals c.RoleId
    where role.RoleName == "ADMIN"
    SELECT new {c.Name, c.Age, role.RoleName, c.IsEmployed};

var filteredClassB = from c in allClassBs
    join role in allRoles on role.Id equals c.RoleId
    where role.RoleName == "ADMIN"
    SELECT new {c.Name, c.Age, role.RoleName, IsEmployed = "-"};

Then I can execute or combine the results into one variable as follows:

var result = filteredClassA.Union(filteredClassB);

I don't like this solution, is there a better way to do all this in a single LINQ query?

Thanks in advance.

Answer 1

Another option, in only one request:

var result = allClassAs.Join(allRoles, c => c.Id, role => role.Id, (c, role) => new {c.Name, c.Age, role.RoleName, c.IsEmployed})
                        .Where(r => r.RoleName == "ADMIN")
                        .Union(allClassBs.Join(allRoles, c => c.Id, role => role.Id, (c, role) => new {c.Name, c.Age, role.RoleName, IsEmployed = "-"})
                        .Where(r => r.RoleName == "ADMIN"));

Answer 2

(from a in context.A
where a.Roles.Any(r=>r.RoleName == "ADMIN")
select new {Name = a.Name, Age = a.Age, Role = "ADMIN", Employed = a.IsEmployed})
.Union(
from b in context.B
where b.Roles.Any(r=>r.RoleName == "ADMIN")
select new {Name = b.Name, Age = b.Age, Role = "ADMIN".RoleName, Employed = "-"}
)