CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
VladN
VladN

Reputation: 739

Linq to entities group join on multiple tables

I'm trying to perform a group join on multiple tables with one to many relations, using into instead of group by. But something is not right. I get duplicate records for each Role a user has.

from compUsr in Repository.All<CompanyUser>()
join usr in Repository.All<User>() on compUsr.UserId equals usr.Id
join usrRole in Repository.All<UserRole>() on usr.Id equals usrRole.UserId
join role in Repository.All<Role>() on usrRoles.RoleId equals role.Id into roles
    select new UserDTO()
    {
        Id = usr.Id,
        Email = usr.Email
        Roles = roles.Select(r => new RoleDTO()
        {
            Id = r.Id
        })
    }

If I remove the join on Role table, and place the into statement on UserRole, the grouping works like a charm, but UserRole is just a linking table, so the Role table is the one I'm interested in. Any ideas how to group this as simple as possible? Thanks!

Upvotes: 2

Views: 5707

Answers (3)

Jair Marques
Jair Marques

Reputation: 1

from compUsr in Repository.All<CompanyUser>()
join usr in Repository.All<User>() on compUsr.UserId equals usr.Id into eGroup
from u in eGroup.DefaultIfEmpty()
join usrRole in Repository.All<UserRole>() on u.Id equals usrRole.UserId into eGroup1
from ur in eGroup1.DefaultIfEmpty()
join role in Repository.All<Role>() on ur.RoleId equals role.Id into eGroup2
from r in eGroup2.DefaultIfEmpty()
group new { u, r } by u into grp
select new
{
      Id = grp.Key.Id,
      Email = grp.Key.Email,
      Roles = grp.FirstOrDefault().r.Id
};

Upvotes: -1

Gert Arnold
Gert Arnold

Reputation: 109080

Navigation properties are there for a reason. They make code much more terse and declarative.

With navigation properties this would be easy:

from usr in context.Users // or Repository.All<User>()
select new UserDto
{
    Id = usr.Id,
    Email = usr.Email,
    Roles = usr.UserRoles.Select(ur => ur.Roles)
               .Select(r => new RoleDTO()
                            {
                                Id = r.Id
                            }
}

I don't know why you also join in CompanyUser (you don't seem to use it), but if you need it, you should start the query there and use navigation properties to get to the other entities.

Also, I assume you have more Role properties in RoleDto. If not, you don't need to select Role entities, because UserRoles already contains the Role's Id.

So it's up to you. You can hang on to the creed that a repository call shall be scoped to exactly one entity (a very narrow definition of "single responsibility"), or use navigation properties for what they were invented for, and consider an aggregate root responsible for the children it encapsulates.

Upvotes: 1

Oleg
Oleg

Reputation: 1458

from compUsr in Repository.All<CompanyUser>()
join usr in Repository.All<User>() on compUsr.UserId equals usr.Id
join usrRole in Repository.All<UserRole>() on usr.Id equals usrRole.UserId
join role in Repository.All<Role>() on usrRoles.RoleId equals role.Id
group new { usr, role } by usr into grp
                    select new
                    {
                        Id = grp.Key.Id,
                        Email = grp.Key.Email,
                        Roles = grp.Select(r => new RoleDTO()
                        {
                            Id = r.role.Id
                        })
                    };

Upvotes: 4

Related Questions