在LINQ中加入多个表并按ID进行分组 [英] Join multi tables and group by Id in LINQ
本文介绍了在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;组; }
}
更新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; }
}
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
这篇关于在LINQ中加入多个表并按ID进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
