How to rewrite SQL Union of two tables with Join in LINQ?

c#, linq

Solution

Another option, in only one query:

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"));

Problem

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 on the DBMS. I currently have an SQL query which I would like to rewrite in LINQ (as elegantly 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 concat or union the results into one variable like so: ``` var result = filteredClassA.Union(filteredClassB); ``` I don't like this solution, is there a better way to do all the above in a single LINQ query? Thanks in advance.

Original source