Asp.Net .Net 5 Lambda LINQ get the role name associated with the user account in Asp.Net identities

Question

I am using Asp.Net .Net5 with Entity framework 5

I have 3 tables

1. aspnetuser
2. aspnetroles
3. aspnetuserRoles = link table

My current LINQ code returns back all data from users and userRoles, but none from the aspnetrole table. I want it to return back the user and the role they are currently assigned so that I can see if they are admin or standard.

    public async Task<IList<User>> GetAllEnabledAccounts()
    {
        var users = await _context.Users
            .Where(u => u.IsEnabled == true)
            .Include(r => r.UserRoles)
            .ToListAsync();
                    
        return users;
    }      

aspnetuser table

id | username
--------------
1  | Jim
2  | Harry

aspnetRoles

id | name
----------
1  | admin
2  | standard

aspnetuserRoles

userId | roleId
----------------
   1   |   1
   2   |   2

When querying it should return back the user Jim showing that he is admin and Harry showing that he is a standard account. How do I type up the LINQ query to output the information correctly?

Answer 1

According to your code, I suppose the aspnetuser and aspnetRoles was configured many-to-many relationship, right? If that is the case, you could refer to the following sample, and using the InClude, ThenInClude and SelectMany method to query the related entities.

Sample code as below (The Authors and Books table contains many to many relationship, BookAuthor is the join table, similar with the aspnetuserRoles table):

        var result = _dbcontext.Authors
            .Include(c => c.BookAuthor)
            .ThenInclude(c => c.Book)
            .SelectMany(c => c.BookAuthor.Select(d => new BookAuthorViewModel()
            {
                Id = d.Author.Id,
                AuthorName = d.Author.AuthorName,
                BookName = d.Book.BookName,
                ISBN = d.Book.ISBN
            })).ToList(); 

The Models as below：

    public class Book
    {
        [Key]
        public int Id { get; set; }

        public string BookName { get; set; }
        public string ISBN { get; set; }

        public IList<BookAuthor> BookAuthor { get; set; }

    }

    public class Author
    {
        [Key]
        public int Id { get; set; }
        public string AuthorName { get; set; }

        public IList<BookAuthor> BookAuthor { get; set; }

    } 

    public class BookAuthor
    {
        public int BookId { get; set; }
        public Book Book { get; set; }

        public int AuthorId { get; set; }
        public Author Author { get; set; }
    }

Create a ViewModel to display the query result.

    public class BookAuthorViewModel
    {
        [Key]
        public int Id { get; set; }
        public string BookName { get; set; }
        public string AuthorName { get; set; }
        public string ISBN { get; set; }
    }

Then, the output like this: