在所选主要类别的所有子类别中获取所有产品的最佳方法是什么?
这是我的类文件结构:
public partial class Category { public int Id { get; set; } public string Name { get; set; } public int ParentCategoryId { get; set; } //reference to Id public ICollection<Category> _subcategories; } public partial class ProductCategory { public int Id { get; set; } public int ProductId { get; set; } public int CategoryId { get; set; } public virtual Category Category { get; set; } public virtual Product Product { get; set; } } public partial class Product { public int Id { get; set; } public string Name { get; set; } public ICollection<ProductViewMap> _productViewmap; } public class ProductViewMap { public int ProductId { get; set; } public int ProductViewCount { get; set; }//indicated how many times product has been viewed means most popular product. public virtual Product Product { get; set; } }这是我尝试过的:
//List to hold all Category Ids of Parent Category Id say for eg:1 List<int> categoryChildList = new List<int>(); var data = (from temp in context.Category where temp.ParentCategoryId == parentCategoryId select new { CategoryId = temp.Id }); if(data.Count() > 0) { foreach (var cat in data) { int _cat = Convert.ToInt32(cat.CategoryId); categoryChildList.Add(_cat); } var tmpList = (from p in Context.ProductCategory join m in context.Product on p.ProductId equals m.Id join n in context.ProductViewMap on m.Id equals n.ProductId where categoryChildList.Contains(p.CategoryId) select m).ToList();这里出现错误:
对象引用未设置为对象的实例。**
当我删除这一行时,一切正常:
join n in context.ProductViewMap on m.Id equals n.ProductId任何帮助将不胜感激。
包含样本记录的Sql小提琴: http ://www.sqlfiddle.com/#!3 / bde6b
如果输入为: 计算机 (parentCategoryId:1),则输出如下
最终输出:
ProductId ProductName
1马力
2比较
3联想
What would be the best way to get all the products in all the child categories of a selected main category?
This is my Class File Structure:
public partial class Category { public int Id { get; set; } public string Name { get; set; } public int ParentCategoryId { get; set; } //reference to Id public ICollection<Category> _subcategories; } public partial class ProductCategory { public int Id { get; set; } public int ProductId { get; set; } public int CategoryId { get; set; } public virtual Category Category { get; set; } public virtual Product Product { get; set; } } public partial class Product { public int Id { get; set; } public string Name { get; set; } public ICollection<ProductViewMap> _productViewmap; } public class ProductViewMap { public int ProductId { get; set; } public int ProductViewCount { get; set; }//indicated how many times product has been viewed means most popular product. public virtual Product Product { get; set; } }This is what i have tried:
//List to hold all Category Ids of Parent Category Id say for eg:1 List<int> categoryChildList = new List<int>(); var data = (from temp in context.Category where temp.ParentCategoryId == parentCategoryId select new { CategoryId = temp.Id }); if(data.Count() > 0) { foreach (var cat in data) { int _cat = Convert.ToInt32(cat.CategoryId); categoryChildList.Add(_cat); } var tmpList = (from p in Context.ProductCategory join m in context.Product on p.ProductId equals m.Id join n in context.ProductViewMap on m.Id equals n.ProductId where categoryChildList.Contains(p.CategoryId) select m).ToList();Here error is coming:
Object reference not set to instance of object.**
When i am removing this line then everything works fine:
join n in context.ProductViewMap on m.Id equals n.ProductIdany help would be greatly appreciated.
Sql fiddle which contain sample records:http://www.sqlfiddle.com/#!3/bde6b
If Input is :Computer(parentCategoryId:1) then output is as below
Final output:
ProductId ProductName
1 hp
2 compaq
3 lenovo
最满意答案
如果您要尝试的只是获取Product记录,那么使用扩展方法语法可以这样做:
var products = context.ProductCategory.Where(pc => pc.Category.ParentCategoryID != null && pc.Category.ParentCategoryID == parentCategoryID) .Select(pc => pc.Product) .Distinct() .ToList() .OrderBy(p => p.ProductViewMap.Max(pvm => pvm.ProductViewCount);所有连接都将由LINQ to Entities生成的SQL查询处理。
If all you're trying to do is to grab the Product records, then using the Extension Method syntax you could do it this way:
var products = context.ProductCategory.Where(pc => pc.Category.ParentCategoryID != null && pc.Category.ParentCategoryID == parentCategoryID) .Select(pc => pc.Product) .Distinct() .ToList() .OrderBy(p => p.ProductViewMap.Max(pvm => pvm.ProductViewCount);All of the joins will be taken care of by the SQL query generated by LINQ to Entities.
更多推荐
发布评论