LinqToSql - Getting a list of distinct IDs from a nested list

Question

I have a property within a class, that I need to iterate through all the possible distinct IDs from a relationship.

I am within a User, which belongs to a Company. Companies, have many solutions and each solution has many "SolutionPortals". What I want, is a list of all distinct "PortalIds" from the "SolutionPortals" (SolutionPortal.PortalID) that are in the DB.

I can't for the life of me get this as a single list. I keep getting:

var solutionIds = from s in this.Company.Solutions.Select(s=>s.SolutionPortals)
                  select s.Select(sp=> sp.PortalID);

Of course this makes sense, since there is a list of Solutions, with a List of SolutionPortals, but I need to select JUST the IDS out into their own list.

IEnumerable<IEnumerable<int>> // <-- Don't want this
IEnumerable<int> // <-- I want this

Any help would be EXTREMELY appreciated.

Thanks/

Answer 1

You probably need something like this:

var listOfIds = listOfSolutionPortals.SelectMany(sps => sps.Solutions)
                                     .Select(sp => sp.PortalId);

Answer 2

SelectMany is the key here:

var solutionIds = this.Company
                      .Solutions
                      .SelectMany(s=>s.SolutionPortals)
                      .Select(sp => sp.PortalId)
                      .Distinct();

Answer 3

This worked:

var solutionIds = (from s in this.Company.Solutions
                   .SelectMany(s => s.SolutionPortals)
                   select s.PortalID).Distinct();

Thanks Mehrdad!