CODEWITHSUNDEEP
c#linq
Ian
Ian

Reputation: 1504

LINQ return parent with subset of children based on grandchild criteria

I can't work out how accomplish this LINQ query. I have a database structured like so:

User  ->  UserAccess  <-  Terminal  <-  Site

Terminals belong to sites, UserAccess specifies which users have access to which terminals. For a given user, I would like to retrieve all available terminals by site. Given Sites already maintain a collection of Terminals, this would be a Site[] but with only a subset of Terminals populated.

I started from (I think) the wrong end, and I am able to retrieve a list of Sites, and filter on the child 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();
}

But I can't filter the Terminals by their child collection UserAccess.

I decided to try again querying from the middle, and I think I'm getting close. I can now retrieve a list of Terminals but I can't retrieve a list of Sites:

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

I've tried adding .GroupBy(t => t.Site) but that creates an anonymous type, when I really want my existing type populated with these results. Also WCF had conniptions when I tried to return IGrouping<Site, Terminal>.

I have the feeling that if I ever manage to work out a solution on my own it will be 11 chained commands, when there is a way to do it in 2, and it would make people wince to see it.

The answer to Linq query to return grandparent, parent and grandchild based on a grandchild's property generates an anonymous type for each result, you get hundreds of Terminals each with it's individual site, no grouping.

The question Linq query to return filtered data is what I'm looking to do, but (like him) I want to avoid having to iterate the data explicitly.

Edit: An attempt to provide a Minimal, Complete, and Verifiable example. Unfortunately it's pretty horrible. I'm trying to replicate the existing class structure I have, which are LINQ to SQL models. It generates circular references (Terminal has a foreign key for Site, but Site has a collection of 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; }
}

Upvotes: 0

Views: 739

Answers (1)

Ian
Ian

Reputation: 1504

Like my brother-in-arms @DeclanMcD in the linked question, I had to resort to explicitly filtering the data. I feel this solution might be a little cleaner, but I still don't like it:

// 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());

The outer foreach could probably be LINQ-ified, but then I wouldn't be able to read it anymore.

Any better answer will get votes and appreciation.

Upvotes: 1

Related Questions