subQuery using Linq

Question

// sql returns records
SELECT * FROM InfoDetails WHERE InfoId =( SELECT tOP 1 InfoId FROM InfoMaster WHERE recordid=1 ORDER BY CreationDate DESC)
// returns 0 records
db.InfoDetails.Where(u => u.InfoId ==(db.InfoMaster
.Where(x => x.recordid == Id).OrderByDescending(x => x.CreationDate).Select(x => x.InfoId).FirstOrDefault()))
.Select(t=> new InfoListViewModel

i need the above sql as linq lambda expression
sql returns reocrds but linq returns 0

Nilesh Shah · Answer

your LINQ query seems to be correct however when you day the SQL query works but the LINQ does not I want to highlight that in SQL Query you have used recordid = 1 which may be returning record as there are records with recordid = 1 while in LINQ you are using a variable make sure to run the SQL query with value of that variable may be there are no records in table with that id being passed to LINQ?

S VK · Answer

i tried by hardcoding the value also but still no results

S VK · Answer

both the replies r giving count as 0

Tapan Patel · Answer

Can you try something like this? var id = db.InfoMaster.Where(x1 => x1.recordid == 1).OrderByDescending(x2 => x2.CreationDate).Select(x3 => x3.InfoId).FirstOrDefault(); var record =db.InfoDetails.Where(u => u.InfoId == id);