How to get data from Junction Table using DB First Approach in MVC

Question

I have Many to many Relationship between 2 tables and i want to retrieve data from junction table.

I'm sharing some source first

Image of Database tables:

https://imgur.com/BDvPiQa

Image of EF Model Entities:

https://i.sstatic.net/4qHOO.jpg

Image of error when debug:

https://i.sstatic.net/QIIJS.jpg

I am using following rules or strategies

+Database First Approach

+EF Diagram

+Custom Role Provider

Problem is that while I'm trying to retrieve data from Junction Table (user_has_role), I am getting SQL Query rather than Data.###

SQL Query which I get:

SELECT \r\n [Extent1].[user_id] AS [user_id], \r\n [Extent2].[user_role_name] AS [user_role_name]\r\n FROM [dbo].[user_has_role] AS [Extent1]\r\n INNER JOIN [dbo].[user_role] AS [Extent2] ON [Extent1].[user_role_id] = [Extent2].[user_role_id]

Here is some code

Role Provider Class is Derived From RoleProvider Class

public class AppRolesProvider : RoleProvider

All methods are override but working with only

public override string[] GetRolesForUser(string username)
        {
            //get all user data from user table for id based on user email
            var _user = db.users.Where(u => u.user_email == username).FirstOrDefault();

           var _role = (from s in db.users
                         where (
                           from c in s.user_role
                           where s.user_id == _user.user_id
                           select c
                           ).Any()
                         select s).ToString();

            string[] roleName = { _role };   

            if (roleName != null)
            {
                return roleName;
            }
            else
            {
                roleName = null;
                return roleName;
            }
        }

I want actual roleName against user (one user have many roles or maybe one)

Updated

Updated db image

and try to retrieve data from junction table but getting this query

"SELECT \r\n [Extent1].[user_id] AS [user_id], \r\n [Extent2].[user_role_name] AS [user_role_name]\r\n FROM [dbo].[user_has_role] AS [Extent1]\r\n INNER JOIN [dbo].[user_role] AS [Extent2] ON [Extent1].[user_role_id] = [Extent2].[user_role_id]\r\n WHERE [Extent1].[user_id] = @p__linq__0"

not data

Here is my method to get role in string array

public override string[] GetRolesForUser(string username)
    {
        //get all user data from user table for id based on user email
        int _user_id = Convert.ToInt32(db.users.Where(u => u.user_email == username).Select(i => i.user_id).FirstOrDefault());

        // Get role from user_has_role against user id
        var _roles = (from _uhr in db.user_has_role
                      join _r in db.user_role on _uhr.user_role_id equals _r.user_role_id
                      where _uhr.user_id == _user_id
                      select new
                      {
                          _r.user_role_name
                      }).ToString();

        // store selected
        string[] roleName = { _roles };  

        if (roleName != null)
        {
            return roleName;
        }
        else
        {
            roleName = null;
            return roleName;
        }
    }

output image in debug mode

Updated

EDMX Image

Answer 1

The junction table was not included by Entity Framework because it did not have a primary key. you fix this problem by adding a primary key to the junction table then update your Edmx file.

After updating model, you can retrieve data from your junction table with a simple linq query.

The difference between the two cases:

1) if your junction table doesn't contain a primary key, EF will generate two class with many to many relation
2) if your junction table contain a primary key, EF will generate 3 Class with: Junction table in one to many relation with user and one to many with user_role

Update

Try with this please :

public override string[] GetRolesForUser(string username)
    {
        //get all user data from user table for id based on user email
        int _user_id = Convert.ToInt32(db.users.Where(u => u.user_email == username).Select(i => i.user_id).FirstOrDefault());

        // Get role from user_has_role against user id
        var _role = db.user_has_role.Where(r => r.user_id == _user_id).Select(r => r.user_role.user_role_name);

        // store selected
        string[] roleName = _role.ToArray();

        if (roleName != null)
        {
            return roleName;
        }
        else
        {
            roleName = null;
            return roleName;
        }
    }