locked
Query across related entities RRS feed

  • Question

  • User269881539 posted

    I have a project with related entities of Product and ProductCategory - whereby a product can feature in multiple categories - what I am having issues with is querying across so that I can pull out all products in a certain category.

    My entity classes are as follows :

    public class Product
    {
    	public int ProductID { get; set; }
    	public string ProductCode { get; set; }
    	public string Description { get; set; }
    	[ForeignKey("ProductID")]
    	public IEnumerable<ProductCategory> ProductCategories { get; set; }
    }
    
    public class ProductCategory
    {
    	[Key]
    	public Guid CategoryItemGuid { get; set; }
    	public int ProductID { get; set; }
    	public int CategoryID { get; set; }
    	public Product Product { get; set; }
    	public virtual Category Category { get; set; }
    }

    I have data access setup using repository pattern / DbContext  (I can add my code for this if required). In my controller where I am trying to populate my ViewModel I cannot get access to the properties of ProductCategory - where ProductCategories.CategoryID is below that is what I think I need but intellisense does not offer it and of course it errors when debugging :

    public ViewResult List(int id = 0, int page = 1)
    {
    
    	CategoryViewModel model = new CategoryViewModel
    	{
    		Category = repository.Categories.FirstOrDefault(c => c.CategoryID == id),
    		Products = prodrepository.Products
    		.Where(p => id == null || p.ProductCategories.CategoryID == id)
    		.OrderBy(p => p.ProductID)
    		.Skip((page - 1) * PageSize)
    		.Take(PageSize),
    		CurrentCategoryID = id //pass in to model
    
    	};
    	return View(model);
    }
    

    The error is :

    Error CS1061 'IEnumerable<ProductCategory>' does not contain a definition for 'CategoryID' and no extension method 'CategoryID' accepting a first argument of type 'IEnumerable<ProductCategory>' could be found (are you missing a using directive or an assembly reference?)

    My ViewModel is below :

    using System.Collections.Generic;
    using TooledUp.Domain.Entities;
    
    namespace TooledUp.WebUI.ViewModels
    {
    	public class CategoryViewModel
    	{
    		public Category Category { get; set; }
    		public IEnumerable<Product> Products { get; set; }
    		//to communicate current category to the view
    		public int CurrentCategoryID { get; set; }
    	}
    }

    It's likely I am expecting EF to be doing too much in the background hooking things up for me, as I'm quite new to all this having relied on ADO for ages.

    Thanks.

    Wednesday, November 4, 2015 10:45 AM

Answers

  • User269881539 posted

    This seems to have fixed it - changing IEnumerable for ICollection :

    public class Product
    {
    	public int ProductID { get; set; }
    	public string ProductCode { get; set; }
    	public string Description { get; set; }
    	[ForeignKey("ProductID")]
    	public ICollection<ProductCategory> ProductCategories { get; set; }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 5, 2015 8:51 AM

All replies

  • User-271186128 posted

    Hi chilluk,

    Error CS1061 'IEnumerable<ProductCategory>' does not contain a definition for 'CategoryID' and no extension method 'CategoryID' accepting a first argument of type 'IEnumerable<ProductCategory>' could be found (are you missing a using directive or an assembly reference?)

    As for this issue, please check the following code:

    public class Product
    {
    	public int ProductID { get; set; }
    	public string ProductCode { get; set; }
    	public string Description { get; set; }
    [ForeignKey("ProductID")] public IEnumerable<ProductCategory> ProductCategories { get; set; } }

    As we can see the ProductCategories property is an IEnumerable<ProductCategory> type, so it not contain a definition for 'CategoryID'.

    I suggest you could try to use the following code:

    .Where(p => id == null || p.ProductCategories.Where(d => d.CategoryID == id).Count() > 0)

    Best regards,
    Dillion

    Wednesday, November 4, 2015 9:05 PM
  • User269881539 posted

    I had tried something similar - nothing seems to bring up the properties under ProductCategories in intellisense, and putting it in manually so I have :

    CategoryViewModel model = new CategoryViewModel
    {
    	Category = repository.Categories.FirstOrDefault(c => c.CategoryID == id),
    	Products = prodrepository.Products
    	.Where(p => id == null || p.ProductCategories.Where(d => d.CategoryID == id).Count() > 0)
    	.OrderBy(p => p.ProductID)
    	.Skip((page - 1) * PageSize)
    	.Take(PageSize)
    
    };

    Triggers the error :

    The specified type member 'ProductCategories' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

    Thursday, November 5, 2015 4:33 AM
  • User269881539 posted

    This seems to have fixed it - changing IEnumerable for ICollection :

    public class Product
    {
    	public int ProductID { get; set; }
    	public string ProductCode { get; set; }
    	public string Description { get; set; }
    	[ForeignKey("ProductID")]
    	public ICollection<ProductCategory> ProductCategories { get; set; }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 5, 2015 8:51 AM