C# Entity framework incorrect query result

Question

I am using entity framework in C# with the following code.

 public List<UserSessionModell> getSessions()
    {
        var result = (from x in db.users
                      from y in db.psw
                      from z in db.users_data
                      from t in db.user_roles
                      from u in db.roles
                      select new UserSessionModell
                      {
                          User_id = x.id,
                          UserName = x.username,
                          Password = y.psw1,
                          Work_id = z.IsEmployedAt,
                          Role = t.role,
                          RoleName = u.id.ToString()
                      }).ToList();

        return result;
    }

The tables in question are connected as follows:

Every property receives the correct data except from the "roles" table which always gets the last record in the table. Please help, what can be the reason?

Answer 1

1. Attempt to debug this database model in Microsoft SQL Server Management Studio.

If there is a problem with syntax SSMS should validate it.

2. Check if there is more then one role for users.

Usually, the problem is not in the syntax, rather than in human thoughts about predicted values.

3. Walk slowly, create select from the bottom of the tower.

Select values just from roles. If it will be correct then try to go upper levels. roles with user_roles. Then roles with user_roles with users. Maybe there is a problem with values in your database

Answer 2

I think you should join tables.

var result = (from user in db.users
                      join psw in db.psw on user.user_id equals psw.user_id 
                      join userdata in db.users_data on userdata.user_id equals psw.user_id 
                      join userrole in db.user_roles on userdata.user_id equals userrole .user_id
                      join role in db.roles on role.id equals userrole .role_id
                      select new UserSessionModell
                      {
                          User_id = user.id,
                          UserName = user.username,
                          Password = psw.psw1,
                          Work_id = userdata.IsEmployedAt,
                          Role = userrole.role,
                          RoleName = role.id.ToString()
                      }).ToList();