c# LINQ query from master and masterdetail table

Question

I have two tables.

One is Master

Id	Date
1	2022-03-12
2	2022-02-14
3	2021-10-15
4	2021-04-09
5	2020-06-24

Another one is Detail

Id	MasterId	Name	Quantity
1	1	item1	25
2	1	item2	30
3	1	item3	20
4	2	item1	25
5	2	item2	20
6	3	item1	35
7	4	item4	25
8	5	item1	25
9	5	item3	29

From above two table I need a query which will give me 3rd table

Year	item1	item2	item3	item4
2020	25	0	29	0
2021	35	0	0	25
2022	50	50	20	0

Answer 1

You want a pivot table

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication18
{
    class Program
    {
        public static void Main(String[] args)
        {
            DataTable masterTable = new DataTable("Master");
            masterTable.Columns.Add("Id", typeof(int));
            masterTable.Columns.Add("Date", typeof(DateTime));

            masterTable.Rows.Add(new object[] { 1, DateTime.Parse("2022-03-12") });
            masterTable.Rows.Add(new object[] { 2, DateTime.Parse("2022-02-14") });
            masterTable.Rows.Add(new object[] { 3, DateTime.Parse("2021-10-15") });
            masterTable.Rows.Add(new object[] { 4, DateTime.Parse("2021-04-09") });
            masterTable.Rows.Add(new object[] { 5, DateTime.Parse("2020-06-24") });

            DataTable detailTable = new DataTable("Detail");
            detailTable.Columns.Add("Id", typeof(int));
            detailTable.Columns.Add("IMasterId", typeof(int));
            detailTable.Columns.Add("Name", typeof(string));
            detailTable.Columns.Add("Quantity", typeof(int));

            detailTable.Rows.Add(new object[] { 1, 1, "item1", 25 });
            detailTable.Rows.Add(new object[] { 2, 1, "item2", 30 });
            detailTable.Rows.Add(new object[] { 3, 1, "item3", 20 });
            detailTable.Rows.Add(new object[] { 4, 2, "item1", 25 });
            detailTable.Rows.Add(new object[] { 5, 2, "item2", 20 });
            detailTable.Rows.Add(new object[] { 6, 3, "item1", 35 });
            detailTable.Rows.Add(new object[] { 7, 4, "item4", 25 });
            detailTable.Rows.Add(new object[] { 8, 5, "item1", 25 });
            detailTable.Rows.Add(new object[] { 9, 5, "item3", 29 });

            string[] items = detailTable.AsEnumerable().Select(x => x.Field<string>("Name")).OrderBy(x => x).Distinct().ToArray();

            DataTable pivot = new DataTable("Pivot");
            pivot.Columns.Add("Year", typeof(int));
            foreach (string item in items)
            {
                pivot.Columns.Add(item, typeof(int));
            }

            var joinTable = (from m in masterTable.AsEnumerable().OrderBy(x => x.Field<DateTime>("Date"))
                join d in detailTable.AsEnumerable() on m.Field<int>("Id") equals d.Field<int>("Id")
                select new {id = m.Field<int>("Id"), year = m.Field<DateTime>("Date").Year, d = d}
                ).GroupBy(x => x.year).ToList();

            foreach (var date in joinTable)
            {
                DataRow row = pivot.Rows.Add();
                row["Year"] = date.Key;
                var names = date.GroupBy(x => x.d.Field<string>("Name")).Select(x => new {name = x.Key, quant = x.Sum(y => y.d.Field<int>("Quantity"))});
                foreach (var name in names)
                {
                    row[name.name] = name.quant;
                }
            }

        }
    }
}