I’m not under any illusions that anyone actually reads this blog, I mainly use it as a place to keep my discoveries, as I have a lousy memory, and would never be able to find them otherwise!
A case in point was a data query I was asked to do this afternoon. A project’s database includes a table of users, and a table of countries, with a joining table to specify which users are responsible for which countries (this is a many-to-many relationship for reasons that are not relevant to this post). The client wanted a spreadsheet where each row contained one country.
This sounded similar in structure to a query I did some time ago, where I discovered an overload of the Linq SelectMany method that I had never used before. Sadly, I had not blogged about it, so couldn’t find the notes. Not making that mistake again, so without further waffle, here is a hopefully useful use of SelectMany. I’m assuming you understand the basic use of the method, so won’t explain that.
Straight to the code…
Users.Where(u => ...various criteria to get the right users...)
.SelectMany(
u => u.UsersCountries.Select(uuc => uuc.Country.Name),
(u, c) => new {
u.Firstname,
u.Surname,
Country = c
}
)
.OrderBy(u => u.Surname)
.ThenBy(u => u.Country)
.Select(u => new {
Name = u.Firstname + " " + u.Surname,
u.Country
})
It’s the SelectMany that is of interest, everything after that is just tidying up the data.
The first lambda passed in selects the nested data, in this case the countries. If we were only interested in this, then it would look like a regular use of SelectMany. It’s the addition of the second lambda that makes this interesting. This gets passed the op-level entity (in our case the user), along with the data selected in the first lambda. This allows us to add the user’s name to the data flow, which we can then use in the final Select. The results look like this (where the names have been changed to protect the innocent)…
What’s even more interesting (if this sort of stuff interests you, but then I guess you wouldn’t have got this far if it didn’t) is there is yet another overload that includes the index of the nested data as well. To explain, if we change the first lambda to look like this…
.SelectMany(
u => u.UsersCountries.Select((uuc, n) => new {
uuc.Country.Name,
n
}),
…then the data flow includes a zero-based count of the countries (ie restarting at zero for each new user). This is passed to the second lambda, and so can be includes in our final data…
(u, c) => new {
u.ID,
u.Firstname,
u.Surname,
Country = c.Name,
N = c.n
}
The resultant data now looks like this…
At this point, you’re probably thinking “So what?” Well, if we utilise the index, we can make the output look much neater. Let’s change the final Select a little…
.Select(u => new {
Name = u.N == 0 ? u.Firstname + " " + u.Surname : "",
u.Country,
})
…then we get the following, much neater output…
This is much neater.
For completeness, here is the full query…
Users.Where(u => /* various criteria to get the right users */)
.ToList()
.SelectMany(
u => u.UsersCountries.Select((uuc, n) => new {
uuc.Country.Name,
n
}),
(u, c) => new {
u.ID,
u.Firstname,
u.Surname,
Country = c.Name,
N = c.n
}
)
.OrderBy(u => u.Surname)
.ThenBy(u => u.N)
.Select(u => new {
Name = u.N == 0 ? u.Firstname + " " + u.Surname : "",
u.Country
})
Be First to Comment