LINQ 根据孙子条件返回具有子项子集的父项

我无法弄清楚如何完成此 LINQ 查询。 我有一个数据库结构如下：

User  ->  UserAccess  <-  Terminal  <-  Site

终端属于站点，UserAccess 指定哪些用户有权访问哪些终端。 对于给定的用户，我想按站点检索所有可用的终端。 鉴于Site已经维护了一个Terminals集合，这将是一个Site[]，但只填充了Terminals的一个子集。

我

从（我认为）错误的一端开始，我能够检索Sites列表，并过滤子Terminals：

using (MsSqlDataContextDataContext db = new MsSqlDataContextDataContext())
{
    // This is part of a WCF Data Service.  If I don't LoadWith I have lazy-loading problems
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<site>(s => s.Terminals);
    // I found AssociateWith can filter on a child collection, but I can't work out
    // how to do the same for grand-child.
    options.AssociateWith<site>(s => s.Terminals.Where(t => t.Active));
    db.LoadOptions = options;
    return db.sites.Where(s => s.Active).ToArray();
}

但是我无法按其子集合UserAccess过滤Terminals。

我

决定再次尝试从中间查询，我想我越来越接近了。 我现在可以检索Terminals列表，但无法检索Sites列表：

return db.UserAccess.Where(a => a.Username.Equals(username) && a.HasAccess ))
                    .Select(a => a.Terminal).ToArray();

我尝试添加.GroupBy(t => t.Site)但是当我真的希望用这些结果填充我的现有类型时，这会创建一个匿名类型。 当我试图返回IGrouping<Site, Terminal>时，WCF也有联系。

我有一种感觉，如果我设法自己制定出一个解决方案，它将是 11 个链式命令，而有一种方法可以在 2 中做到这一点，它会让人们看到它畏缩。

Linq 查询的答案是根据孙子的属性返回祖父、父和孙子，为每个结果生成一个匿名类型，您将获得数百个终端，每个终端都有单独的站点，没有分组。

Linq 查询返回过滤数据的问题就是我要做的，但（像他一样）我想避免显式迭代数据。

编辑：尝试提供最小、完整和可验证的示例。 不幸的是，这非常可怕。 我正在尝试复制我现有的类结构，即 LINQ to SQL 模型。 它生成循环引用（Terminal有一个用于Site的外键，但Site有一个Terminals的集合）。

public void LinqQuery()
{
    Site s1 = new Site { SiteName = "Site 1", Terminals = new List<Terminal>() };
    Site s2 = new Site { SiteName = "Site 2", Terminals = new List<Terminal>() };
    Terminal t1 = new Terminal { TerminalName = "Terminal 1:1", site = s1, AccessList = new List<UserAccess>() };
    Terminal t2 = new Terminal { TerminalName = "Terminal 2:1", site = s1, AccessList = new List<UserAccess>() };
    s1.Terminals.Add(t1);
    s1.Terminals.Add(t2);
    Terminal t3 = new Terminal { TerminalName = "Terminal 3:2", site = s2, AccessList = new List<UserAccess>() };
    Terminal t4 = new Terminal { TerminalName = "Terminal 4:2", site = s2, AccessList = new List<UserAccess>() };
    s2.Terminals.Add(t3);
    s2.Terminals.Add(t4);
    User u1 = new User { Name = "Ian" };
    UserAccess ua1 = new UserAccess { user = u1, terminal = t1 };
    t1.AccessList.Add(ua1);
    UserAccess ua2 = new UserAccess { user = u1, terminal = t2 };
    t2.AccessList.Add(ua2);
    UserAccess ua3 = new UserAccess { user = u1, terminal = t4 };
    t4.AccessList.Add(ua3);
    Site[] allSites = {s1, s2};
    Terminal[] allTerminals = {t1, t2, t3, t4};
    UserAccess[] allUserAccess = {ua1, ua2, ua3};
    // This gives me a list of all terminals where user "Ian" has access (3 Terminals)
    var v = allUserAccess.Where(ua => ua.user.Name.Equals("Ian")).Select(ua => ua.terminal).ToArray();
    // If I take it one step further and Select the site, I end up with an array of 3 sites (only 2 exist)
    var v2 = v.Select(t => t.site).ToArray();
    // What I want is the allSites array but with the terminals filtered.
}
internal class Site
{
    public string SiteName { get; set; }
    public List<Terminal> Terminals { get; set; }
}
internal class Terminal
{
    public string TerminalName { get; set; }
    public Site site { get; set; }
    public List<UserAccess> AccessList { get; set; }
}
internal class UserAccess
{
    public User user { get; set; }
    public Terminal terminal { get; set; }
}
internal class User
{
    public string Name { get; set; }
}

// What I want is the allSites array but with the terminals filtered.
List<Site> toReturn = new List<Site>(allSites);
foreach (Site s in toReturn)
{
    // Look through every Terminal
    // And check if they have any UserAccess that would allow access.
    // If no access (!t.AccessList.Any), remove this Terminal.
    s.Terminals.RemoveAll(t => !t.AccessList.Any(
        a => a.user.Name.Equals("Ian")));
}
// Also if I want empty Sites removed:
toReturn.RemoveAll(s => !s.Terminals.Any());