c# - 如何在 LINQ 中用 Join 重写两个表的 SQL Union？

Question

我有 3 个类如下:

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;}
    ...
}

假设这些类在 DBMS 上有它们自己的表。

我目前有一个 SQL 查询，我想在 LINQ 中重写(尽可能优雅)

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'

目前我已经设法将其重写为:

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 = "-"};

然后我可以像这样将结果连接或联合到一个变量中:

var result = filteredClassA.Union(filteredClassB);

我不喜欢这个解决方案，是否有更好的方法在单个 LINQ 查询中完成上述所有操作？

提前致谢。

Answer 1

另一种选择，仅在一个查询中:

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