Join multi tables and group by Id in LINQ

Question

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>
    }

Check this: http://stackoverflow.com/questions/7325278/group-by-in-linq — Chandu, Jan 06 '15 at 04:33
@Enigmativity: Which overload http://msdn.microsoft.com/en-us/library/system.linq.enumerable.groupby%28v=vs.110%29.aspx do you mean? — abatishchev, Jan 06 '15 at 04:37
@abatishchev - `GroupBy(Of TSource, TKey, TElement)(IEnumerable(Of TSource), Func(Of TSource, TKey), Func(Of TSource, TElement))`. — Enigmativity, Jan 06 '15 at 05:47

Ehsan Sajjad · Accepted Answer · 2015-01-07T07:06:45.910

1

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

edited Jan 07 '15 at 07:06

answered Jan 06 '15 at 05:07

Ehsan Sajjad

61,834
16
105
160

If you see the OPs view closely, he is using a single `foreach` loop and not nested one, this is what I exactly posted as answer but OP edited his question again. – Rahul Singh Jan 06 '15 at 05:25
@RahulSingh where OP said that hw wants to avoid nested loop, proper way in this case is grouping, instead of duplicating again and again cateogry object – Ehsan Sajjad Jan 06 '15 at 05:28
I knw that's the proper way but I was just pointing to what OP has posted in the view. I knw that's problem with the design :) – Rahul Singh Jan 06 '15 at 05:32
Yup, I Know either OP need should change the model or need to show duplicates. – Rahul Singh Jan 06 '15 at 05:41
thank for support from @EhsanSajjad. Your code it's work fine :) – Khiem Nguyen Jan 06 '15 at 06:26
@EhsanSajjad I have a question for you. Really necessary nested ProductModel and CategoryModel in GroupModel. Have another way for solution ? – Khiem Nguyen Jan 06 '15 at 06:45
according to the data, it is the better way to do it instead of duplicating data – Ehsan Sajjad Jan 06 '15 at 06:46
Hi @EhsanSajjad I have just added a ColorModel for my question. How to display color's name of product. I tried but it only group by color. it not grouping by category's name. Can you help me edit code for this solution ? – Khiem Nguyen Jan 07 '15 at 03:00
@KhiemNguyen join on colorslist and group this way:https://dotnetfiddle.net/dE7qyD – Ehsan Sajjad Jan 07 '15 at 05:26
thank @EhsanSajjad but you should check my display result in my question. I want to my view display like that. Your code don't group category's name – Khiem Nguyen Jan 07 '15 at 06:13
@KhiemNguyen it is grouping category name – Ehsan Sajjad Jan 07 '15 at 06:19
@EhsanSajjad your code in dotnetfiddle display 2 categorys name are Laptop and you're grouping color. But I want to display only 1 line for each category name and color name display beside product name. You can check view display result in my question. – Khiem Nguyen Jan 07 '15 at 06:52
@KhiemNguyen your are confused i think, you just need to do display logic by yourself data is correct , see:https://dotnetfiddle.net/dE7qyD – Ehsan Sajjad Jan 07 '15 at 07:04
@EhsanSajjad :) I'm not confused. In Update 2 and Your output. I see you display 2 CategoryName: Laptop when you add color name for each product. Have you way to display 1 CategoryName: Laptop. If you cannot do it maybe my data have problem with my logic – Khiem Nguyen Jan 07 '15 at 07:29
@KhiemNguyen create a custom class in which put ``colorname`` also as property – Ehsan Sajjad Jan 07 '15 at 07:38
@EhsanSajjad I have puted a string colorname {get; set;} in class GroupModel but It still not change. Can you demo by code ? – Khiem Nguyen Jan 07 '15 at 07:48
@KhiemNguyen here is example how you can work around :https://dotnetfiddle.net/NspTcC – Ehsan Sajjad Jan 07 '15 at 07:54

Join multi tables and group by Id in LINQ

1 Answers1

UPDATE:

Model:

UPDATE 2:

OUTPUT:

Linked

Related