使用linq mvc c#从数据库检索图像

Question

我有2个表ProductDetails和图像，我保存缩略图图像到ProductsDetail表和所有其他相关图像到图像表。保存很好，但我正在与图像检索作斗争。我想从图像表而不是从productDetail表中检索产品的缩略图图像。关于这个逻辑，我需要帮助，请帮助一些代码示例。另外，请让我知道逻辑是正确的还是错误的?请看下面的课程

public class ProductDetail
{
public int pro_id { get; set; }
public string pro_name { get; set; }              
public string pro_model { get; set; }
public string pro_Dimensions { get; set; }            
public string pro_imageTitle { get; set; }   // (thumbnail image)
public string pro_image { get; set; }               
public string pro_desc { get; set; }
public Nullable<double> pro_price { get; set; }         
public int pro_UnitsInStock { get; set; }
public Nullable<double> pro_oldprice { get; set; } 
public virtual ICollection<Images> tbl_Images { get; set; }
}

public class Images
{
public int ImageID { get; set; }
public int pro_id { get; set; }
public string ImageTitle { get; set; }
public string ImagePath { get; set; }
}

目前，我已经尝试了一些linq代码，但它返回重复的产品，我只想为每个产品显示1个缩略图。

IEnumerable<ProductList> List =
(from  prod in this.storeDB.tbl_Product  
join img in this.storeDB.tbl_Images on prod.pro_id 
equals img.pro_id
select new ProductList
{
pro_id = prod.pro_id,
pro_name = prod.pro_name,
pro_model = prod.pro_model,
pro_Dimensions = prod.pro_dimensions,
pro_UnitsInStock = (int)prod.pro_UnitsInStock,
pro_oldprice = prod.pro_oldprice,
pro_price = (double)prod.pro_price,
pro_isFeatured = prod.pro_isFeatured,
pro_isOnSale = prod.pro_isOnSale,
pro_desc = prod.pro_desc,
ImageID  =img.img_id,
ImageTitle = img.ImageTitle                                                      
});

Answer 1

在图像表中可能有几个图像返回重复的值。

第一组图像然后用product对结果进行join表。

IEnumerable<ProductList> List =
(from img in this.storeDB.tbl_Images
group img by img.pro_id into imggr
join prod in this.storeDB.tbl_Product on imggr.pro_id
equals prod.pro_id
select new ProductList
{
pro_id = prod.pro_id,
pro_name = prod.pro_name,
pro_model = prod.pro_model,
pro_Dimensions = prod.pro_dimensions,
pro_UnitsInStock = (int)prod.pro_UnitsInStock,
pro_oldprice = prod.pro_oldprice,
pro_price = (double)prod.pro_price,
pro_isFeatured = prod.pro_isFeatured,
pro_isOnSale = prod.pro_isOnSale,
pro_desc = prod.pro_desc,
ImageID = imggr.FirstOrDefault().img_id,
ImageTitle = imggr.FirstOrDefault().ImageTitle
});

Answer 2

我通过在图像表中添加缩略图图像标志来解决这个问题。