Linq select in list with 2 many-to-many relationships

Question

I have the following database structure:

USER <--> [user_client] <--> CLIENT <--> [client_application] <--> APPLICATION

USER, CLIENT and APPLICATION tables contain unique keys. user_client and client_application are many-to-many tables to map USERs to CLIENTs and CLIENTs to APPs.

I am using MVC5 / C#. The many-to-many tables are hidden in my Model by the Entity Framework.

What I want to achieve is the following: for a given USER, which has a list of CLIENTs, get the combined list of the distinct APPLICATIONs that all his CLIENTs have.

Could you please help with the logic and the Linq query (preferably in fluent syntax if possible)? Is it possible to do it in a single query without looping through the list of clients?

Thank you in advance.

Reda

score 2 · Accepted Answer · answered Aug 12 '15 at 06:58

2

Not sure it matches your schema but what about

user.clients.SelectMany(c => c.applications).Distinct()

answered Aug 12 '15 at 06:58

tafia

1,512
9
18

Works perfectly. Thank you. – Reda Aug 12 '15 at 08:09

Jürgen Steinblock · Answer 2 · 2015-08-12T09:23:06.220

1

The key is to use SelectMany instead of Select which will give you a IEnuerable<Application> instead of a IEnumerable<IEnumerable<Application>>

var user = context.Users.Where(u => u.Id == 1).Single();
var applications = user.Clients
    .SelectMany(c => c.Application)
    .GroupBy(a = a.Id)
    .Select(a => a.First());

edited Aug 12 '15 at 09:23

answered Aug 12 '15 at 06:58

Jürgen Steinblock

30,746
24
119
189

beat me too it , that could all actually be one lambda expression as well – Scott Selby Aug 12 '15 at 06:59
missing the `Distinct()` – tafia Aug 12 '15 at 07:00
but is it list of Applications that ALL users have? I see right now that it is list of applications that AT LEAST ONE user have. – teo van kot Aug 12 '15 at 07:02
@tafia thats true, fixed that with an GroupBy because I am not sure how a Distinct would behave (Even if it is the same record it could be another object) – Jürgen Steinblock Aug 12 '15 at 07:03
Both `GroupBy` and `Distinct` will use an `IEqualityComparer`. I don't really see the difference – tafia Aug 12 '15 at 07:07
@tafia With GroupBy I can specify that I want to group by a specific value (the id). The default comparison (if a class does not implement IComparable would fallback to `obj1.Equals(obj2)` which will check the HashCode. As I said, I don't know if client1.Application with id 1 is the same object as client2.Application with id 1 for entity framework, so I would feel save with group by. – Jürgen Steinblock Aug 12 '15 at 07:21
@SchlaWiener, yes, as long as the Id has a default `IEqualityComparer` (which is probably true for a `int`). We could also have Application override `Equals` and `GetHashCode` if really needed. It looks *better*. But yeah, all in all it is equivalent – tafia Aug 12 '15 at 07:26
@fafia In one project with entity framework (version 5) I had to implement `IEquatable` in my entity classes this to work. You can not just assume OP did this, too. The `Distinct` extension method does not know about the Id and does not allow a `Distinct(x => x.Id)` overload (this can be achived with `DistinctBy` from https://code.google.com/p/morelinq/ – Jürgen Steinblock Aug 12 '15 at 07:49

score 0 · Answer 3 · answered Sep 15 '22 at 19:18

I want to collaborate to this question, not providing an exact solution, but additional information.

I applied @tafia answer to my own problem, but with a slight modification to the SelectMany method. I replaced it with just Select.

File selectedFile = _unitOfWork.FileRepository.GetById(idFile)
selectedFile.FilePaper.Select(c => c.Paper).Distinct().ToList()

You can read about the difference between Select and SelectMany, here.

I applied my modified solution on the following group of tables:

Though your table seems to be different:

I'm not sure if that solution proposed by @tafia works there.

PS. If you want to make the middle tables appear in EF, a possible "fix" is adding a primary key to them (id).

Linq select in list with 2 many-to-many relationships

3 Answers3