select subcategory list inside category list

Question

this is my query for selecting categories and subcategories. List lstcat = new List (); var categ = _db.Category .Select(c => new Category() { PKCatId = c.PKCatId, CatName = c.CatName, Icon = c.Icon, ActiveIcon = c.ActiveIcon, Subcat = GetChildren(_db, c.PKCatId) }) .ToList(); public static List GetChildren(HomeDBContext _db, int parentId) { return _db.subCat.Where(c => c.PKCatId == parentId) .Select(c => new SubCat { PKSubCatId = c.PKSubCatId, SubCatName = c.SubCatName, PKCatId = c.PKCatId }) .ToList(); } It gives error like "The entity or complex type 'EcomHomeFurniture.Models.Category' cannot be constructed in a LINQ to Entities query." can anyone help me to solve this?

Ruchi Sharavat · Answer

Not sure for the solution but can try by using viewmodel for subcat instead of direct entity: public class SubCatViewModel { public int PKSubCatId { get ; set ;} public string SubCatName { get ; set ;} public int PKCatId{ get ; set ;} } public static List GetChildren(HomeDBContext _db, int parentId) { var subcatlist= _db.subCat.Where(c => c.PKCatId == parentId) .Select(c => new SubCatViewModel { PKSubCatId = c.PKSubCatId, SubCatName = c.SubCatName, PKCatId = c.PKCatId }) .ToList(); return subcatlist; } Or can try this too: public static List GetChildren(HomeDBContext _db, int parentId) { var list= _db.subCat.Where(c => c.PKCatId == parentId).ToList(); var subcatlist=list .Select(c => new SubCat { PKSubCatId = c.PKSubCatId, SubCatName = c.SubCatName, PKCatId = c.PKCatId }) .ToList(); return subcatlist; }

Karthik Chintala · Answer

The error clearly states that you cannot use an Entity inside of the select query. I think your Category class is a mapped entity for your DbContext. You cannot perform projection onto your mapped entities in EF. However, you can project onto a DTO or an anonymous type. All you've to do is create another class, which is not a mapped entity or just project anonymously. This is what you've to do public class CategoryDTO { public long PKCatId { get ; set ;} public string CatName { get ; set ;} public SubCategoryDTO Subcat { get ; set ;} //... etc } public class SubCategoryDTO { public long PKSubCatId { get ; set ;} public string SubCatName { get ; set ;} //... etc } public void Method1(){ List lstcat = new List (); var categ = _db.Category .Select(c => new CategoryDTO() { PKCatId = c.PKCatId, CatName = c.CatName, // Icon = c.Icon, // ActiveIcon = c.ActiveIcon, Subcat = GetChildren(_db, c.PKCatId) })ToList(); } public static List GetChildren(HomeDBContext _db, int parentId) { return _db.subCat.Where(c => c.PKCatId == parentId) .Select(c => new SubCategoryDTO { PKSubCatId = c.PKSubCatId, SubCatName = c.SubCatName // PKCatId = c.PKCatId }).ToList(); } If you don't want to create DTO classes you could return properties anonymously. Hope this helps.