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?

Józef Podlecki · Accepted Answer · 2020-05-13 13:37:48Z

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

answered May 13, 2020 at 13:37

Józef Podlecki

11.4k5 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eryk Korzeniowski · Accepted Answer · 2020-05-13 14:27:20Z

2

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

answered May 13, 2020 at 14:27

Eryk Korzeniowski

813 bronze badges

Collectives™ on Stack Overflow

C# Entity framework incorrect query result

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related