CODEWITHSUNDEEP
entity-frameworklinqmodelentity-framework-core
Raphael Lima
Raphael Lima

Reputation: 179

EF CORE Select distinct grandchildren with many-to-may relationship

I'm trying to learn EF Core and hit this wall since I'm also fairly new to LINQ

Consider the model: enter image description here

I'm trying to get all the distinct users from a single company;

The SQL statement would be something like this:

SELECT DISTINCT gau.AppUserId, au.Name, au.Id FROM Companies c 
INNER JOIN Groups g ON g.CompanyId = c.Id
INNER JOIN GroupAppUsers gau ON gau.GroupId = g.Id
INNER JOIN AppUsers au ON gau.AppUserId = au.Id
Where c.Id = 40

Result: enter image description here

How would I build this query like this? (Without the includes)

            return await context.Companies
            .Include(g => g.Groups)
                .ThenInclude(au => au.AppUsers)
                    .ThenInclude(u => u.AppUser)
            .SingleOrDefaultAsync(x => x.Id == id);

*Also, I'm not sure about the DB Model, I'm trying to avoid circular references but I think I should put Users linked with Companies instead of Groups, what do you think??

Upvotes: 1

Views: 1240

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205729

I'm trying to get all the distinct users from a single company

Rather than starting from companies and navigating to users, thus multiplying the users due to many-to-many relationship and then applying Disctinct operator, you could simply start from users and apply Any based criteria, thus eliminating the need of Disctinct at all.

Something like this (the DbSet / navigation property names could be different):

var companyUsers = await context.Users
    .Where(u => u.UserGroups.Any(ug => ug.Group.Company.Id == id))
    .ToListAsync();

Upvotes: 3

NetMage
NetMage

Reputation: 26927

Once you have the single Companies object, you can use the Navigation properties to get the AppUser objects:

return await context.Companies
                    .Include(g => g.Groups)
                    .ThenInclude(au => au.AppUsers)
                    .ThenInclude(u => u.AppUser)
                    .SingleOrDefaultAsync(x => x.Id == id)
                    .Groups.AppUsers.Distinct();

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89266

Assuming your linking table (GroupAppUser) isn't modeled as an entity, something like:

var q = from c in db.Companies
        from g in c.Groups
        from u in g.AppUsers
        select u;

or in Lambda form:

var q = db.Companies
          .SelectMany(c => c.Groups)
          .SelectMany(g => g.AppUsers);

Upvotes: 0

Related Questions