using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TestLinq
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {            
            DataTable dt = new DataTable();
            dt.Columns.Add("product");
            dt.Columns.Add("val1",typeof(int));
            dt.Columns.Add("val2",typeof(int));
            dt.Rows.Add("honey", 1, 1);
            dt.Rows.Add("honey", 5, 7);
            dt.Rows.Add("milk", 10, 45);
            dt.Rows.Add("butter", 15, 7);
            dt.Rows.Add("milk", 21, 13);
            dt.Rows.Add("butter", 5, 17);
            var var1 = dt.AsEnumerable()
                .GroupBy(p => p.Field<String>("product"))
                .Select(g=> 
                    new {product = g.Key, 
                        val1 = g.Average(p=>p.Field<int>("val1")), 
                        val2 = g.Average(p=>p.Field<int>("val2"))
                    }).ToList();
                        
                dataGridView1.DataSource = var1; 
           
        }

    }    
    
}

DataTable dtMaster = new DataTable();
            dtMaster.Columns.Add("id", typeof(int));
            dtMaster.Columns.Add("product");
            dtMaster.Rows.Add(1, "honey");
            dtMaster.Rows.Add(2, "milk");
            dtMaster.Rows.Add(3, "butter");
            
            DataTable dtDetail = new DataTable();
            dtDetail.Columns.Add("id", typeof(int));
            dtDetail.Columns.Add("val1",typeof(int));
            dtDetail.Columns.Add("val2",typeof(int));
            dtDetail.Rows.Add(1, 1, 1);
            dtDetail.Rows.Add(1, 5, 7);
            dtDetail.Rows.Add(2, 10, 45);
            dtDetail.Rows.Add(3, 15, 7);
            dtDetail.Rows.Add(2, 21, 13);
            dtDetail.Rows.Add(3, 5, 17);
            
            var var2 = dtMaster.AsEnumerable()
                .GroupJoin(dtDetail.AsEnumerable(), k => k.Field<int>("id"), k2 => k2.Field<int>("id"),
                (k, k2) => new
                {
                    product = k.Field<String>("product"),
                    avgVal1 = k2.Average(r => r.Field<int>("val1")),
                    avgVal2 = k2.Average(r => r.Field<int>("val2"))
                })
                .ToList();
                        
                dataGridView1.DataSource = var2;