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

Answer 1

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?

Answer 2

i tried by hardcoding the value also but still no results

Answer 3

both the replies r giving count as 0

Answer 4

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