Getting related data through navigation in linq

Question

I want to display a list of category names associated with a product. In my system, a product can be linked to any number of categories. These are my entity models:

public class Product
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Info { get; set; }
    public decimal Price { get; set; }
    public List<ProductInCategory> InCategories { get; set; }
}

public class ProductInCategory
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int SortOrder { get; set; }
    public int ProductCategoryId { get; set; }
    public Product Product { get; set; } // Nav.prop. to product
    public ProductCategory ProductCategory { get; set; } // Nav.prop. to category
}

public class ProductCategory
{
    public int Id { get; set; }
    public int SortOrder { get; set; }
    public string Title { get; set; }

    [ForeignKey(nameof(ParentCategory))]
    public int? ParentId { get; set; }

    public ProductCategory ParentCategory { get; set; } //nav.prop to parent
    public ICollection<ProductCategory> Children { get; set; } //nav. prop to children

    public List<ProductInCategory> ProductInCategory { get; set; }
}

EDIT This is my UPDATED viewmodel:

public class ViewModelProduct
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Info { get; set; }
    public decimal Price { get; set; }
    public int SortOrder { get; set; }
    public List<ProductInCategory> InCategories { get; set; }
    public IEnumerable<ProductCategory> Categories { get; set; }
}

At present, I have this query, which retrieves the product with a list of category Ids in InCategories:

var product = _context.Products
            .Select(p => new ViewModelProduct
            {
                Id = p.Id,
                Title = p.Title,
                Info = p.Info,
                Price = p.Price,
                InCategories = p.InCategories
            })
            .SingleOrDefault(m => m.Id == id);

What needs to be added in order to also get Title from ProductCategory for each ProductCategoryId in ProductInCategory?

Also, I'm not sure if I like the method-syntax. In order to do joins, I think query syntax is better, because of readability. I have tried to make a query syntax version of my query, but I don't know how to put it into ViewModelProduct, so it just fails when sent to the view:

var product = from p in _context.Products
          where p.Id == id
          select new
          {
              p.Id,
              p.Info,
              p.Title,
              p.Price,
              p.InCategories
          };

Answer 1

I think this is solution for you: EDIT:

public class ViewModelCategoryWithTitle
{
    public int Id { get; set; }
    public string Title { get; set; }
}
public class ViewModelProduct
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Info { get; set; }
    public decimal Price { get; set; }
    public int SortOrder { get; set; }
    public IEnumerable<ViewModelCategoryWithTitle> Categories { get; set; }
}

EDIT This is for IEnumerable ViewModelCategoryWithTitle Categories (my ViewModel):

  var product = (from p in _context.Products
              where p.Id == id
              select new ViewModelProduct
              {
                Id = p.Id,
                Title = p.Title,
                Info = p.Info,
                Price = p.Price,
                Categories = p.InCategories.Select(
                  inCategory=> new ViewModelCategoryWithTitle
                  {
                     CategoryId = inCategory.ProductCategory.Id,
                     Title = inCategory.ProductCategory.Title
                  })
              }).SingleOrDefaultAsync();

EDIT This is for your updated ViewModel:

  var product = (from p in _context.Products
              where p.Id == id
              select new ViewModelProduct
              {
                Id = p.Id,
                Title = p.Title,
                Info = p.Info,
                Price = p.Price,
                Categories = p.InCategories.Select(
                  inCategory=> inCategory.ProductCategory)
              }).SingleOrDefaultAsync();