Linq query to explicitly load related data

c# entity-framework-core linq

Question

I'm using EF Core to work against a pre-existing database. It's a pretty common model that uses a lot of join tables, specific to my case, a join table for users to roles. The UserRole table has just two columns - a UserId and a RoleId.

The code below shows my latest attempt at gathering all the data together into a single DTO object, with one exception - I can't seem to load the Roles associated with the RoleId(s) that appear in the UserRoles collection.

Any suggestions on how I can load the associated Role collection? I'm going have to iterate through the Role collection to add the friendly name of the role to the user claims, and right now, I'm just ending up with a big fat null.

var validatedUser = _context.User
                .Where(x => string.Equals(x.UserName, username, StringComparison.CurrentCultureIgnoreCase) && x.Active)
                .Include(x => x.UserRole)
                .ThenInclude(x => x.Role)
                .Select(result => new ValidatedUser
                {
                    Id = result.Id,
                    Email = result.Email,
                    FirstName = result.FirstName,
                    LastName = result.LastName,
                    PhoneNumber = result.PhoneNumber,
                    Username = result.UserName,
                    UserRoles = result.UserRole,
                    //Roles = result.Role, // this is where failure happens
                    PasswordHash = result.PasswordHash
                })
                .FirstOrDefault();

Edit 1 - showing the modelBuilder for the UserRole

modelBuilder.Entity<UserRole>(entity =>
{
    entity.HasKey(e => new { e.UserId, e.RoleId });
    entity.Property(e => e.UserId).HasColumnName("UserID");
    entity.Property(e => e.RoleId).HasColumnName("RoleID");
    entity.HasOne(d => d.Role)
        .WithMany(p => p.UserRole)
        .HasForeignKey(d => d.RoleId)
        .HasConstraintName("FK_UserRole_RoleID");
     entity.HasOne(d => d.User)
        .WithMany(p => p.UserRole)
        .HasForeignKey(d => d.UserId)
        .HasConstraintName("FK_UserRole_UserID");
});

Here's the ValidatedUser class:

public class ValidatedUser
{
    public Guid Id { get; set; }
    public string Username { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PasswordHash { get; set; }
    public string FullName => $"{FirstName} {LastName}";
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
    public string FormattedPhoneNumber
    {
        get
        {
            if (string.IsNullOrEmpty(PhoneNumber)) return PhoneNumber;

            switch (PhoneNumber.Length)
            {
                case 10:
                    return $"({PhoneNumber.Substring(0, 3)}) {PhoneNumber.Substring(3, 3)}-{PhoneNumber.Substring(6, 4)}";
                default:
                    return PhoneNumber;
            }
        }
    }

    public ICollection<UserRole> UserRoles { get; set; }
    public ICollection<Role> Roles { get; set; }
}
1
0
7/9/2018 3:24:00 PM

Accepted Answer

First of all the Include & ThenInclude statements are unnecessary since you're doing a select statement that will be evaluated server side.

For the query, what about this:

var validatedUser = 
    _context.User
    .Where(x => string.Equals(x.UserName, username, StringComparison.CurrentCultureIgnoreCase) && x.Active)
    .Select(result => new ValidatedUser
    {
        Id = result.Id,
        Email = result.Email,
        FirstName = result.FirstName,
        LastName = result.LastName,
        PhoneNumber = result.PhoneNumber,
        Username = result.UserName,
        Roles = result.UserRoles.Select(ur => ur.Role),
        PasswordHash = result.PasswordHash
     }).FirstOrDefault();

If this doesn't work, you could always map the roles client side (in this case you do need the Include/thenInclude():

var validatedUser = 
    _context.User
    .Include(u => u.UserRoles).ThenInclude(ur => ur.Role)
    .Where(x => string.Equals(x.UserName, username, StringComparison.CurrentCultureIgnoreCase) && x.Active)
    .ToList() // Force execution of query here...
    .Select(result => new ValidatedUser // From here on it's client side
    {
        Id = result.Id,
        Email = result.Email,
        FirstName = result.FirstName,
        LastName = result.LastName,
        PhoneNumber = result.PhoneNumber,
        Username = result.UserName,
        UserRoles = result.UserRoles.ToList(),
        Roles = result.UserRoles.Select(ur => ur.Role).ToList(),
        PasswordHash = result.PasswordHash
     }).FirstOrDefault();
1
7/9/2018 5:13:32 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow