How can I write the nested linq query

Question

How can I write the below SQL query in linq?

select * from Employee where Email = (select User_Email from tbl_Login where User_Email='abc@demo.com' and User_Password = 'demo123')

What I have done is:

from tblemp in ctx.Employees where tblemp.Email = (from tblLogin in ctx.tbl_Login where (tblLogin.User_Email == login.User_Email && tblLogin.User_Password == login.User_Password))

However, it is throwing an error.

Answer 1

In method syntax:

var employee = ctx.Employee
  .Where(e => e.Email == ctx.tbl_Login
     .Single(l => l.User_Email = "abc@demo.com" and l.User_Password = "demo123")
     .User_Email)

This returns an IEnumerable. If you are expecting a single result use Single instead of Where. You can also use First to take the first result from that set.