在 LINQ 中按 Id 连接多表和分组

我想按categoryId显示列表产品的名称组，这是我的代码：

我想要我的视图显示结果：

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

我的组模型：

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

我的控制器：

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());

这是我绑定列表的观点。我使用 GroupModel 来嵌套 ProductModel 和 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>
    }

您需要左外连接Products with Categories并将产品分组到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; }
}

工作示例小提琴 https://dotnetfiddle.net/5rmblj

更新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:

类别名称： 智能手机
产品： |_ Lumia 720 - 黄色
产品： |_ Lumia 920 - 黄色
类别名称： 桌面
产品： |_ PC HP - 蓝色
产品： |_ 戴尔 PC - 蓝色
类别名称： 笔记本电脑
产品： |_ 笔记本电脑 Lenovo - 黄色
类别名称： 笔记本电脑
产品： |_ 戴尔笔记本电脑 - 红色
产品： |_ HP 笔记本电脑 - 红色