locked
LINQ inner join RRS feed

  • Question

  • User521171331 posted

    Hi,

    I am still quite new in LINQ. I have this code which authenticate user name and password. I need to inner join with another role table. Say example my  bridge role table is UserRoles, how do I do so in LINQ?

    var loginUser2 = db.LoginUsers.SingleOrDefault(
                    c => c.LoginUsername == loginUser.LoginUsername
                    && c.LoginPassword == loginUser.LoginPassword);

    If in SQL, my SQL inner join would be 

    Select * from LoginUsers u INNER JOIN UserRoles ur ON u.RoleID = ur.RoleID WHERE u.LoginUsername = '' and u.LoginPassword = ''

    Sunday, May 28, 2017 10:09 AM

Answers

  • User-1509636757 posted

     var loginUser2 = from c in db.UserRoless
                                 join cn in db.LoginUsers on c.UserID equals cn.Id
                                 join ct in db.LoginRoles on c.RoleID equals ct.Id
                                 where (cn.LoginUsername == loginUser.LoginUsername
                                     && cn.LoginPassword == loginUser.LoginPassword)
                                 select ct;

    That is also fine. You can write LinQ query instead of Lambda Expression. for the error you are facing, wrap the query with brackets and call FirstOrDefault() method:

     var loginUser2 = (from c in db.UserRoless
                                 join cn in db.LoginUsers on c.UserID equals cn.Id
                                 join ct in db.LoginRoles on c.RoleID equals ct.Id
                                 where (cn.LoginUsername == loginUser.LoginUsername
                                     && cn.LoginPassword == loginUser.LoginPassword)
                                 select ct).FirstOrDefault();
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 28, 2017 3:53 PM

All replies

  • User-1509636757 posted

    Check out this Lambda Expression:

    var dealerInfo = db.LoginUsers
                        .Join(db.UserRoles,
                                t1 => t1.RoleID,
                                t2 => t2.RoleID,
                                (t1, t2) => new { Users = t1, Roles = t2 })
                        .Where(t1_t2 => t1_t2.Users.LoginUsername == loginUser.LoginUsername &&
                                        t1_t2.Users.LoginPassword == loginUser.LoginPassword)
                        .Select(t1_t2 => t1_t2.Users)
                        .FirstOrDefault();

    Sunday, May 28, 2017 2:38 PM
  • User521171331 posted

    What if I join one more table?

    LoginUser: ID

    UserRoles: ID, UserID, RoleID

    LoginRoles: ID, RoleValue

    Sunday, May 28, 2017 3:26 PM
  • User521171331 posted

    I came out with this:

                var loginUser2 = from c in db.UserRoless
                                 join cn in db.LoginUsers on c.UserID equals cn.Id
                                 join ct in db.LoginRoles on c.RoleID equals ct.Id
                                 where (cn.LoginUsername == loginUser.LoginUsername
                                     && cn.LoginPassword == loginUser.LoginPassword)
                                 select ct;
               
    
                if (loginUser2 != null)
                {
                    if (loginUser2.RoleName == "Outlet Admin")

    But there is error at last line "RoleName".

    Sunday, May 28, 2017 3:41 PM
  • User-1509636757 posted

     var loginUser2 = from c in db.UserRoless
                                 join cn in db.LoginUsers on c.UserID equals cn.Id
                                 join ct in db.LoginRoles on c.RoleID equals ct.Id
                                 where (cn.LoginUsername == loginUser.LoginUsername
                                     && cn.LoginPassword == loginUser.LoginPassword)
                                 select ct;

    That is also fine. You can write LinQ query instead of Lambda Expression. for the error you are facing, wrap the query with brackets and call FirstOrDefault() method:

     var loginUser2 = (from c in db.UserRoless
                                 join cn in db.LoginUsers on c.UserID equals cn.Id
                                 join ct in db.LoginRoles on c.RoleID equals ct.Id
                                 where (cn.LoginUsername == loginUser.LoginUsername
                                     && cn.LoginPassword == loginUser.LoginPassword)
                                 select ct).FirstOrDefault();
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 28, 2017 3:53 PM