C# Entity framework incorrect query result

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: enter image description here

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

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();

Leave a Reply

Your email address will not be published. Required fields are marked *