从linq到sql中的子查询中选择前1个结果

Here is my sql query as follow

这是我的SQL查询如下

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

我尝试将此sql语句转换为linq，如下所示

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 .

但问题是它给我一个嵌套列表中的imageName，但我想将imageName作为一个字符串。

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

我还使用快速手表进行检查，在下面的图像中，您可以看到imageName出现在内部列表中。

1 个解决方案

#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:

而不是返回序列IEnumerable 的Take（1），使用返回单个字符串值的FirstOrDefault（）（如果没有结果，则返回null）。也不要为子查询结果创建匿名类型：

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

BTW FirstOrDefault() generates TOP(1) SQL.

BTW FirstOrDefault（）生成TOP（1）SQL。