c# - Joining two tables and selecting a relating value from the first

Question

I have two tables (simplified views):

Product
ProductId (PK)
Name
VariableProduct (Bit)

ProductVariation
ProductVariationId (PK)
Name
ProductId (FK)

ProductImage
ProductImageId (PK)
ImageUrl
ProductId (FK)
ProductVariationId (FK) NULLABLE

I need to join the two tables to output a list of "ProductSearch" which is a simplified view model.

A Product can have a main image, a variation can have its own variation image.

In the join, I would like to specify the parent image for the variable product, if the variation image value is null.

The code I have is as follows:

            //Single products first
            var products = await _context.Product.Where(p => p.Enabled == true && p.VariableProduct == false)
            .Include(e => e.ProductImages.Where(p => p.IsDefault == true && p.ProductVariationId == null))
            .Select(p => new ProductSearch()
            {
                ProductId = p.ProductId,
                ProductVariationId = null,
                Name = p.Name,
                ImageUrl = p.ImageUrl.FirstOrDefault().ImageUrl
            }).ToListAsync();

            //Variables
            var productVariables = await _context.ProductVariation
            .Include(p => p.Product).
            .Include(e => e.ProductImages.Where(p.ProductVariationId == e.ProductVariationId))
            .Select(p => new ProductSearch()
            {
                ProductId = p.ProductId,
                ProductVariationId = p.ProductVariationId,
                Name = p.Product.Name + " (" +  p.Name + ")",
                ImageUrl =  p.ProductImages.Any() ? p.ProductImages.First().ImageUrl : "PARENT IMAGE NEEDED HERE"
            }).ToListAsync();
            var productSearch = products.Union(productVariables).OrderBy(p => p.Name).ToList();

            return productSearch;

question from:https://stackoverflow.com/questions/65873131/joining-two-tables-and-selecting-a-relating-value-from-the-first

Answer 1

Two things:

1. You only need to use .Include(x => x.SomeProperty) when you're letting EF construct the entities, not when you're selecting a NEW object. EF will automatically interpret your select and wire in the nested property data.

Example when you need to include so that product.ProductImages is populated:

var p = _context.Product
    // Without this include, the products in the return list will all have null
    // ProductImages property.
    .Include(p => p.ProductImages) 
    .ToList(); // A list of products WITH their ProductImages property populated.

Example when you don't need to use include (derived object / anonymous object) (obviously untested against your model):

var p = _context.Product
    .Select(p => new { // This could be a defined object too such as ProductSearch...
        p.Id,
        p.Name,
        MyDirectImages = p.ProductImages
            // You don't need to filter to this products images because the 
            // ProductImages navigation property is ALREADY only images for this product.
            .Where(i => i.IsDefault && p.ProductVariationId == null)
            .ToList(), // OK, could be .FirstOrDefault() as well if only expecting one.
        MyVariations = p.ProductVariations
            .Select(v => new {
                v.Id,
                v.Name,
                // Once again, the VariationImages (I'm guessing the name...) navigation
                // property is already only images for this variation.
                ImageUrl = v.VariationImages.Where(x => x.IsDefault).FirstOrDefault().ImageUrl
            }).ToList();

    })
    .ToList(); // A list of information about products

2. Without addressing the other superfluous includes, you can do this for your specific question:

Replace

ImageUrl = p.ProductImages.Any() ? p.ProductImages.First().ImageUrl : "PARENT IMAGE NEEDED HERE"

with

ImageUrl = p.ProductImages.Any() ? p.ProductImages.First().ImageUrl : p.Product.ProductImages.Where(x => x.IsDefault && x.ProductVariationId == null).FirstOrDefault().ImageUrl