How to get a list of IdentityUsers and their Roles at the same time?

asp.net-core-identity c# entity-framework-core roles

Question

Chat message model:

public class ChatMessage
{
    [Key] public long Id { get; set; }

    [Required] public string Content { get; set; }
    [Required] public DateTime TimePosted { get; set; }

    public string AuthorId { get; set; }
    [Required, ForeignKey("authorId")] public ApplicationUser Author { get; set; }
}

Now I want to get a list of chat messages from a given day while knowing if their author is in the role "Voice":

var messages = await dbContext.ChatMessages.AsNoTracking()
    .Where(chm => chm.TimePosted.Date == someDateTime.Date).OrderBy(chm => chm.TimePosted)
    .ToListAsync();

var result = new List<Tuple<ChatMessage, bool>> { };
foreach(var m in messages)
    result.Add(Tuple.Create(m, await userManager.IsInRoleAsync(Author, "Voice")));

As far as I can tell this will send to the database as many queries as there are chat messages to fetch, which seems obnoxious, because this should be done in only one query, shouldn't it? Or at least, a constant number of queries, but not linear.

Am I doing this wrong? If yes, how should I do this?

1
0
9/5/2018 9:43:48 PM

Popular Answer

The correct approach seems to come from @Kirk Larkin's comment, which I'm converting to an answer because it is more visible than a comment and because comments may go away anytime.

He linked this docs page: Migrate authentication and Identity to ASP.NET Core 2.0#Add IdentityUser POCO navigation properties, which says:

The Entity Framework (EF) Core navigation properties of the base IdentityUser POCO (Plain Old CLR Object) have been removed. If your 1.x project used these properties, manually add them back to the 2.0 project: C#

/// <summary>
/// Navigation property for the roles this user belongs to.
/// </summary>
public virtual ICollection<IdentityUserRole<int>> Roles { get; } = new List<IdentityUserRole<int>>();

/// <summary>
/// Navigation property for the claims this user possesses.
/// </summary>
public virtual ICollection<IdentityUserClaim<int>> Claims { get; } = new List<IdentityUserClaim<int>>();

/// <summary>
/// Navigation property for this users login accounts.
/// </summary>
public virtual ICollection<IdentityUserLogin<int>> Logins { get; } = new List<IdentityUserLogin<int>>();

To prevent duplicate foreign keys when running EF Core Migrations, add the following to your IdentityDbContext class' OnModelCreating method (after the base.OnModelCreating(); call): C#

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    // Customize the ASP.NET Core Identity model and override the defaults if needed.
    // For example, you can rename the ASP.NET Core Identity table names and more.
    // Add your customizations after calling base.OnModelCreating(builder);

    builder.Entity<ApplicationUser>()
        .HasMany(e => e.Claims)
        .WithOne()
        .HasForeignKey(e => e.UserId)
        .IsRequired()
        .OnDelete(DeleteBehavior.Cascade);

    builder.Entity<ApplicationUser>()
        .HasMany(e => e.Logins)
        .WithOne()
        .HasForeignKey(e => e.UserId)
        .IsRequired()
        .OnDelete(DeleteBehavior.Cascade);

    builder.Entity<ApplicationUser>()
        .HasMany(e => e.Roles)
        .WithOne()
        .HasForeignKey(e => e.UserId)
        .IsRequired()
        .OnDelete(DeleteBehavior.Cascade);
}

Once these steps are done, I believe writing the correct query should become as trivial as a single Include statement. Brain compiled code below:

var usersAndRoles = await dbContext.ChatMessages.AsNoTracking()
    .Include(msg => msg.Author).ThenInclude(author => author.Roles)
    .Select(msg => new { Message = msg, IsVoice = msg.Author.Roles.Contains("Voice") })
    .ToListAsync();
0
9/17/2018 12:20:33 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