Select top 1 result from subquery in linq to sql

Question

Here is my sql query as follow

select enq_Id,enq_FromName,        enq_EmailId,        enq_Phone,        enq_Subject,        enq_Message,        enq_EnquiryBy,        enq_Mode,        enq_Date,        ProductId,        (select top 1 image_name          from tblProductImage as i          where i.product_id=p.product_Id) as imageName,        p.product_Name,        p.product_code      from tblEnquiry as e   inner join tblProduct as p ON e.ProductId=p.product_Id  where ProductId is not null  

And I try to convert this sql statement into linq as follow

var result = from e in db.tblEnquiries              join d in db.tblProducts                    on e.ProductId equals d.product_Id                                   where e.ProductId != null              orderby e.enq_Date descending              select new {                 e.enq_Id,                 e.enq_FromName,                 e.enq_EmailId,                 e.enq_Phone,                 e.enq_Subject,                 e.enq_Message,                 e.enq_EnquiryBy,                 e.enq_Mode,                 e.enq_Date,                 d.product_Id,                 d.product_Name,                 imageName = (from soh in db.tblProductImages                              where soh.product_id == e.ProductId                              select new { soh.image_name }).Take(1)               }; 

But problem its giving me imageName in a nested list but i want that imageName just as a string .

I also check by using quick watch and in following image you can see that imageName appearing in inner list .

Answer 1

Instead of Take(1) which returns sequence IEnumerable<string>, use FirstOrDefault() which returns single string value (or null if there is no results). Also don't create anonymous type for subquery result:

imageName = (from soh in db.tblProductImages              where soh.product_id == e.ProductId              select soh.image_name).FirstOrDefault() 

BTW FirstOrDefault() generates TOP(1) SQL.