title: How to rewrite SQL Union of two tables with Join in LINQ?
body: <p>I have 3 classes as below:</p>
  
  <pre><code>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;}
      ...
  }
  </code></pre>
  
  <p>Suppose these classes have their own tables on the DBMS.</p>
  
  <p>I currently have an SQL query which I would like to rewrite in LINQ (as elegantly as possible)</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>Currently I have managed to rewrite it as: </p>
  
  <pre><code>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 = "-"};
  </code></pre>
  
  <p>Then I can concat or union the results into one variable like so:</p>
  
  <pre><code>var result = filteredClassA.Union(filteredClassB);
  </code></pre>
  
  <p>I don't like this solution, is there a better way to do all the above in a single LINQ query?</p>
  
  <p>Thanks in advance.</p>