Method based linq queries

Question

I have five tables in database whose Entity classes are as follows -

Product

public int ProductId { get; set; }
public string Name { get; set; }
public int Category { get; set; }
public string Description { get; set; }
public string Brand { get; set; }
public virtual ProductCategory ProductCategory { get; set; }
public virtual ICollection<ProductImage> ProductImages { get; set; }
public virtual ICollection<ProductVariantMapping> ProductVariantMappings 

ProductCategory

public int CategoryId { get; set; }
public string Name { get; set; }
public virtual ICollection<Product> Products { get; set; }

ProductImage

public int ProductImageId { get; set; }
public int ProductId { get; set; }
public byte[] Image { get; set; }
public virtual Product Product { get; set; }

ProductVariantMapping

public int MappingId { get; set; }
public int ProductId { get; set; }
public int ProductVariantId { get; set; }
public string Value { get; set; }
public System.Guid GUID { get; set; }
public virtual Product Product { get; set; }
public virtual ProductVariant ProductVariant { get; set; }

ProductVariant

public int ProductVariantId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public virtual ICollection<ProductVariantMapping> ProductVariantMappings 

I want to get Product Details which should include ProductId, ProductName, Category, Description, Brand, Image(Only 1 for now), and Variants*

*Variants would be a list of all the variants of a product. A single variant can be a combination of all the VariantIds with same GUIDs. (VariantName is in ProductVariant table and VariantValue is in ProductVariantMapping table and Price is in inventory table).

So, I used method-based linq for this purpose.

EkartEntities ekartEntities = new EkartEntities();

var productDetails = ekartEntities.Products.Include(p => 

  p.ProductVariantMappings).Include(p => p.ProductImages).Include(p =>

  p.ProductCategory).Where(p => p.ProductId == 

  productDetailDTO.ProductId).ToList();

Now I have to convert my product into a ProductDetailDTO.

ProductDetailDTO

public class ProductDetailDTO
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public byte[] Image { get; set; }
    public string Description { get; set; }
    public string Brand { get; set; }
    public List<Variant> Variants { get; set; }
}

public class Variant
{
    public string Name { get; set; }
    public string Value { get; set; }
    public System.Guid Guid { get; set; }
    public decimal Price { get; set; }
}

I started doing this like this -

    void ToDTO(List<Product> products)
    {
        EkartEntities ekartEntities = new EkartEntities();
        ProductDetailDTO productDetailDTO = new ProductDetailDTO();
        foreach (var item in products)
        {
            productDetailDTO.ProductId = item.ProductId;
            productDetailDTO.Name = item.Name;
            productDetailDTO.Category = item.ProductCategory.Name;
            productDetailDTO.Description = item.Description;
            productDetailDTO.Brand = item.Brand;
            productDetailDTO.Image = item.ProductImages.ElementAt(0).Image;
            foreach (var variant in item.ProductVariantMappings)
            {
                productDetailDTO.Variants = variant.ProductVariant // ?
            }
        }
    }

I don't know how do I proceed further. How can I extract the variant based on the GUIDs?

Answer 1

The logic of combining of ProductVariant entries with same GUID in mapping table doesn't seem clear from the question, however you can group entries in ProductVariantMappings by GUID and then add any logc you like on group. Here is an example where I take first name and value in a groub of variant with the same GUID:

void ToDTO(List<Product> products)
{
    EkartEntities ekartEntities = new EkartEntities();
    ProductDetailDTO productDetailDTO = new ProductDetailDTO();
    foreach (var item in products)
    {
        productDetailDTO.ProductId = item.ProductId;
        productDetailDTO.Name = item.Name;
        productDetailDTO.Category = item.ProductCategory.Name;
        productDetailDTO.Description = item.Description;
        productDetailDTO.Brand = item.Brand;
        productDetailDTO.Image = item.ProductImages.ElementAt(0).Image;

        productDetailDTO.Variants = item.ProductVariantMappings
            .GroupBy(pm => pm.GUID)
            .Select(g => new Variant
            {
                Guid = g.Key,

                // Here should be some logic for getting a name of the combination of Variants
                // I just take first
                Name = g.FirstOrDefault()?.ProductVariant?.Name,

                // Here should be some logic for getting a value of the combination of Variants
                // Take first again
                Value = g.FirstOrDefault()?.Value,

                Price = // need inventory table to compute price
            })
            .ToList();
    }
}

Also note that you need somehow add relation to inventory table, which is not presented in question. Hope it helps.