在LINQ中加入多个表并按ID进行分组

本文介绍了在LINQ中加入多个表并按ID进行分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我希望以我的视图显示结果：

桌面 | _ PC惠普 - 红色 | _ PC戴尔 - 黄色 | _ PC华硕 - 红色 SmartPhone | _ Lumia 720 - 蓝色

My GroupModel：

public class GroupModel { public Category Categories {get;组; } public Product Products {get;组; } }

我的管制员：

列表与LT;分类> listCategories =新列表<类别> {新类别{Id = 1，CateName =SmartPhone}，新类别{Id = 2，CateName =Laptop}，新类别{ 3，CateName =桌面}，}; 列表<产品> listProducts =新列表<产品> {新产品{Id = 1，ProdName =Lumia 720，CategoryId = 1，ColorId = 2}，新产品{Id = 2，产品名称=PC HP，， CategoryId = 3，ColorId = 1}，新产品{Id = 3，ProdName =PC Dell，CategoryId = 3，ColorId = 1}，新产品{Id = 4，ProdName =笔记本电脑联想，CategoryId = 2，ColorId = 2}，新产品{Id = 5，ProdName =Lumia 920，CategoryId = 1，ColorId = 2}，新产品{Id = 6 ，ProdName =Laptop Dell，CategoryId = 2，ColorId = 3}， new Product {Id = 7，ProdName =Laptop HP，CategoryId = 2，ColorId = 3} }; 列表< Color> listColor = new List< Color> {新颜色{ColorId = 1，ColorName =Blue}，新颜色{ColorId = 2，ColorName =Yellow}，新颜色{ColorId = 3，ColorName =Red} }; var query =从listCategories中的c得到在c.Id的listProducts中加入p等于p.CategoryId 选择新的GroupModel { Categories = c ，产品= p }; return View（query.ToList（））;

这是我的视图来绑定列表。我使用GroupModel来嵌套ProductModel和CategoryModel

@model IEnumerable< Test.Models.GroupModel> @foreach（模型中的var项） {< tr> < td> @ Html.DisplayFor（model => item.Categories.CateName）< / td> < / tr> < tr> < td> @ Html.Label（| __）@ Html.DisplayFor（model => item.Products.ProdName）< / td> < / tr>

解决方案

产品与类别和产品在 CategoryID ： p>

var query =从listProducts中的p 在p.CategoryId的listCategories中加入c等于c.Id到e 从e .DefaultIfEmpty（）组p到p.CategoryId到g 中选择新的{Products = g，CategoryId = g.Key};

更新：

var query =从listProducts中的p 在p.CategoryId中的listCategories中加入c等于c.Id在e.DefaultIfEmpty（）中由j中的e 由新的组p {j.Id，j.CateName}转换为g 选择新的GroupModel { Products = g.ToList（）， CategoryId = g.Key.Id， CateogryName = g.Key.CateName };

型号：

public class GroupModel { public int CategoryId {get;组; } public string CateogryName {get;组; } public List< Product>产品{get;组; } }

Working Example FIDDLE

更新2：

var query = from listProducts join cl in listColor on p.ColorId equals cl.ColorId 在p.CategoryId的listCategories中加入c等于c.Id into e from e.DefaultIfEmpty（）group p by new { j.Id，cl.ColorId，j.CateName，cl.ColorName } 转换为g 选择新的GroupModel { Products = g.ToList（），CategoryId = g.Key.Id，CateogryName = g.Key.CateName，ColorId = g.Key .ColorId，ColorName = g.Key.ColorName } ; foreach（查询中的var项） { Console.WriteLine（CategoryName：{0}，item.CateogryName）; //Console.WriteLine(\"ColorName：{0}，）; foreach（item.Products中的var product） { Console.WriteLine（Product：| _ {0} - {1}，product.ProdName，item.ColorName）; $ / code $ / pre $ b $输出：/ $ 2

CategoryName：智能手机 产品：| _ _ Lumia 720 - 黄色产品： _ Lumia 920 - 黄色 CategoryName：Desktop 产品：| _ PC HP - 蓝色产品：| _ _戴尔 - 蓝色/> 类别名称：笔记本电脑 产品：| _ _联想笔记本电脑 - 黄色表示类别名称：笔记本电脑¥b $ b产品名称： - 红色产品：| _笔记本电脑惠普 - 红色

I want to display a list product's name group by categoryId, this is my code:

I want to my view display result:

Desktop |_ PC HP - Red |_ PC Dell - Yellow |_ PC Asus - Red SmartPhone |_ Lumia 720 - Blue

My GroupModel:

public class GroupModel { public Category Categories { get; set; } public Product Products { get; set; } }

My controller:

List<Category> listCategories = new List<Category> { new Category {Id = 1, CateName = "SmartPhone"}, new Category {Id = 2, CateName = "Laptop"}, new Category {Id = 3, CateName = "Desktop"}, }; List<Product> listProducts = new List<Product> { new Product {Id = 1, ProdName = "Lumia 720", CategoryId = 1, ColorId = 2}, new Product {Id = 2, ProdName = "PC HP", CategoryId = 3, ColorId = 1}, new Product {Id = 3, ProdName = "PC Dell", CategoryId = 3, ColorId = 1}, new Product {Id = 4, ProdName = "Laptop Lenovo", CategoryId = 2, ColorId = 2}, new Product {Id = 5, ProdName = "Lumia 920", CategoryId = 1, ColorId = 2}, new Product {Id = 6, ProdName = "Laptop Dell", CategoryId = 2, ColorId = 3}, new Product {Id = 7, ProdName = "Laptop HP", CategoryId = 2, ColorId = 3} }; List<Color> listColor = new List<Color> { new Color {ColorId = 1, ColorName = "Blue"}, new Color {ColorId = 2, ColorName = "Yellow"}, new Color {ColorId = 3, ColorName = "Red"} }; var query = from c in listCategories join p in listProducts on c.Id equals p.CategoryId select new GroupModel { Categories = c, Products = p }; return View(query.ToList());

and this is my view to bind a list. I'm using GroupModel to nested ProductModel and CategoryModel

@model IEnumerable<Test.Models.GroupModel> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(model => item.Categories.CateName) </td> </tr> <tr> <td> @Html.Label("|__ ") @Html.DisplayFor(model => item.Products.ProdName) </td> </tr> }

解决方案

You need left outer join of Products with Categories and group Products on CategoryID:

var query = from p in listProducts join c in listCategories on p.CategoryId equals c.Id into e from j in e.DefaultIfEmpty() group p by p.CategoryId into g select new { Products = g, CategoryId = g.Key };

UPDATE:

var query = from p in listProducts join c in listCategories on p.CategoryId equals c.Id into e from j in e.DefaultIfEmpty() group p by new { j.Id,j.CateName} into g select new GroupModel { Products = g.ToList(), CategoryId = g.Key.Id, CateogryName=g.Key.CateName };

Model:

public class GroupModel { public int CategoryId { get; set; } public string CateogryName { get; set; } public List<Product> Products { get; set; } }

Working EXAMPLE FIDDLE

UPDATE 2:

var query = from p in listProducts join cl in listColor on p.ColorId equals cl.ColorId join c in listCategories on p.CategoryId equals c.Id into e from j in e.DefaultIfEmpty()group p by new { j.Id,cl.ColorId,j.CateName,cl.ColorName } into g select new GroupModel { Products = g.ToList(), CategoryId = g.Key.Id, CateogryName = g.Key.CateName,ColorId = g.Key.ColorId,ColorName = g.Key.ColorName } ; foreach (var item in query) { Console.WriteLine("CategoryName: {0}", item.CateogryName); //Console.WriteLine("ColorName: {0}", ); foreach(var product in item.Products) { Console.WriteLine("Product: |_ {0} - {1}", product.ProdName,item.ColorName); } }

OUTPUT:

CategoryName: SmartPhone Product: |_ Lumia 720 - Yellow Product: |_ Lumia 920 - Yellow CategoryName: Desktop Product: |_ PC HP - Blue Product: |_ PC Dell - Blue CategoryName: Laptop Product: |_ Laptop Lenovo - Yellow CategoryName: Laptop Product: |_ Laptop Dell - Red Product: |_ Laptop HP - Red