Help needed in SQL query conversion into LINQ

Question

Convert this SQL query into LINQ query where U.IsAdmin, U.IsApprover, U.IsActive, R.IsActive are of bit data type SELECT U.ID, U.FirstName, U.LastName, U.RoleID, U.UserID, U.IsAdmin, U.IsApprover, r.RoleName RoleName FROM USERS U INNER JOIN [ROLE] R ON U.ROLEID = R.ID AND R.IsActive = 1 WHERE UPPER(U.UserID)= @UserID AND U.Password = @Password AND U.IsActive = 1

Shuvojit Halder · Answer

string userid=@ UserID; string Password=@Password; var result=(from l in yourdatacontext. USERS.Where(b=>b.UserID== userid.Toupper() && b.password= Password && b.IsActive = true join m in yourdatacontext. ROLE.Where(a=>a.IsActive = true) on l. ROLEID equals R.ID select new { l.ID, l.FirstName,l.LastName, l.RoleID, l.UserID, l.IsAdmin, l.IsApprover, m.RoleName }); This is your linq query.

Zeeshan Azim · Answer

After bit editing in your query it becomes (from U in _db.Users.Where(b => (b.UserID == loginName) && (b.Password == Password) && (b.IsActive == true)) join R in _db.Roles.Where(a => a.IsActive == true) on U.RoleId equals R.ID select new { U.ID, U.FirstName, U.LastName, U.RoleId, U.UserID, U.isAdmin, U.isApprover, R.RoleName }) I am very thankful to you

Rajeev Punhani · Answer

Hi Zeeshan, Please use the following tool.It may help. http://www.linqpad.net/ If the above solution is helpful then, accept the answer.

Help needed in SQL query conversion into LINQ

Answers (3)