private void Form1_Load(object sender, EventArgs e)
{
// tum urunleri listeleyelim
NorthwindSabahEntities db = new NorthwindSabahEntities();
// product uzerinden ilerlendigi icin tum productlar gozukmeli yani left join
var sorgu1 = db.Products
.Select(x => new
{
x.ProductName,
x.UnitPrice,
x.Category.CategoryName
})
.ToList();
//burada iliski kuruldugu icin iliskideki kurala gore inner join calisir.
var sorgu2 = from p in db.Products
join cat in db.Categories on p.CategoryID equals cat.CategoryID
select new
{
UrunAdi = p.ProductName,
Fiyat = p.UnitPrice,
Kategori = cat.CategoryName
};
dgvTest.DataSource = sorgu2.ToList();
//calisanlarimi email adresleri ile listeleyin
var sorgu3 = db.Employees
.Select(x => new
{
x.FirstName,
x.LastName,
Email = (x.FirstName.Substring(0, 1) + x.LastName + "@northwind.com").ToLower()
}).ToList();
dgvTest.DataSource = sorgu3;
var sorgu4 = from emp in db.Employees
select new
{
emp.FirstName,
emp.LastName,
Email = (emp.FirstName.Substring(0, 1) + emp.LastName + "@northwind.com").ToLower()
};
dgvTest.DataSource = sorgu4.ToList();
this.Text = $"{db.Products.Average(x => x.UnitPrice):c2}";
var sorgu5 = db.Products
.Where(x => x.UnitPrice >= db.Products.Average(y => y.UnitPrice))
.Select(x => new
{
x.ProductName,
Fiyat = x.UnitPrice,
x.Category.CategoryName
})
.OrderByDescending(x => x.Fiyat)
.ToList();
dgvTest.DataSource = sorgu5;
var sorgu6 = from p in db.Products
where p.UnitPrice >= db.Products.Average(x => x.UnitPrice)
orderby p.UnitPrice descending
select new
{
p.ProductName,
Fiyat = p.UnitPrice,
p.Category.CategoryName
};
dgvTest.DataSource = sorgu6.ToList();
//hangi kategoriden kac tane urunum var
var sorgu7 = db.Products
.Where(x => x.CategoryID.HasValue && x.SupplierID.HasValue)
.GroupBy(x => new { x.Category.CategoryName, x.Supplier.CompanyName })
.Select(x => new
{
CategoryName = x.Key.CategoryName,
CompanyName = x.Key.CompanyName,
Total = x.Count()
})
.OrderBy(x => x.CategoryName)
.ThenBy(x => x.CompanyName)
.ToList();
dgvTest.DataSource = sorgu7;
var sorgu8 = from product in db.Products
join category in db.Categories on product.CategoryID equals category.CategoryID
join supp in db.Suppliers on product.SupplierID equals supp.SupplierID
group new
{
category,
supp
} by new
{
category.CategoryName,
supp.CompanyName
}
into gp
orderby gp.Key.CategoryName ascending, gp.Key.CategoryName ascending
select new
{
CategoryName = gp.Key.CategoryName,
CompanyName = gp.Key.CompanyName,
Total = gp.Count()
};
dgvTest.DataSource = sorgu8.ToList();
// hangi urunden ne kadarlik siparis verilmis (tl bazinda)
var sorgu9 = db.Order_Details
.Join(db.Products,
od => od.ProductID,
product => product.ProductID,
(od, product) => new { od, product })
.GroupBy(x => x.product.ProductName)
.OrderBy(x => x.Key)
.ToList()
.Select(x => new
{
x.Key,
Total = Math.Round(x.Sum(y => y.od.UnitPrice * y.od.Quantity * Convert.ToDecimal(1 - y.od.Discount)), 2)
});
dgvTest.DataSource = sorgu9.ToList();
var sorgu10 = from prod in db.Products
join od in db.Order_Details on prod.ProductID equals od.ProductID
group new
{
prod,
od
} by new
{
prod.ProductName
}
into gp
orderby gp.Key.ProductName
select new
{
gp.Key.ProductName,
Total = gp.Sum(x => x.od.UnitPrice * x.od.Quantity)
};
dgvTest.DataSource = sorgu10.ToList();
//var data = sorgu10.ToList();
//dgvTest.DataSource = data.GroupBy(x => x.ProductName)
// .Select(x => new
// {
// ProductName = x.Key,
// Total = Math.Round(x.Sum(y => y.od.UnitPrice * y.od.Quantity * Convert.ToDecimal(1 - y.od.Discount)), 2)
// }).ToList();
// Çalışanlar kaç sipariş almış?
var sorgu11 = from dbEmployee in db.Employees
join dbOrder in db.Orders on dbEmployee.EmployeeID equals dbOrder.EmployeeID
join dbOrderDetails in db.Order_Details on dbOrder.OrderID equals dbOrderDetails.OrderID
group new
{
dbEmployee,
dbOrderDetails
} by new
{
dbEmployee.FirstName,
dbEmployee.LastName
} into gp
select new
{
Employee = gp.Key.FirstName + " " + gp.Key.LastName,
Total = gp.Sum(x => x.dbOrderDetails.Quantity)
};
dgvTest.DataSource = sorgu11.ToList();
var sorgu12 = db.Order_Details
.Join(db.Orders,
od => od.OrderID,
o => o.OrderID,
(od, o) => new { od, o })
.Join(db.Employees,
gg => gg.o.EmployeeID,
emp => emp.EmployeeID,
(gg, emp) => new { gg, emp })
.GroupBy(x => x.emp.FirstName + " " + x.emp.LastName)
.Select(x => new
{
Employee = x.Key,
Total = x.Sum(y => y.gg.od.Quantity)
})
.OrderByDescending(x => x.Total)
.ToList();
dgvTest.DataSource = sorgu12;
// Hangi kategoriden toplam kaç adet sipariş var?
var sorgu13 = from dbCategories in db.Categories
join dbProducts in db.Products on dbCategories.CategoryID equals dbProducts.CategoryID
join dbOrderDetails in db.Order_Details on dbProducts.ProductID equals dbOrderDetails.ProductID
group new
{
dbCategories,
dbOrderDetails
} by new
{
dbCategories.CategoryName
} into gp
select new
{
Category = gp.Key.CategoryName,
Total = gp.Sum(x => x.dbOrderDetails.Quantity)
};
dgvTest.DataSource = sorgu13.ToList();
// Sipariş no - toplam sipariş tutarı
var sorgu14 = from orderDetail in db.Order_Details
group new
{
orderDetail
} by new
{
orderDetail.OrderID
} into gp
select new
{
gp.Key.OrderID,
Total = gp.Sum(x => x.orderDetail.UnitPrice * x.orderDetail.Quantity)
};
var sorgu15 = db.Order_Details
.GroupBy(x => x.OrderID)
.ToList()
.Select(x => new
{
x.Key,
Total = $"{x.Sum(y => y.Quantity * y.UnitPrice * Convert.ToDecimal(1 - y.Discount)):c2}"
}).ToList();
dgvTest.DataSource = sorgu15;
// Çalışanların hangi kategoriden kaç tane sipariş verdiği
var sorgu16 = from dbCategories in db.Categories
join dbProducts in db.Products on dbCategories.CategoryID equals dbProducts.CategoryID
join dbOrderDetails in db.Order_Details on dbProducts.ProductID equals dbOrderDetails.ProductID
join dbEmployees in db.Employees on dbOrderDetails.Order.EmployeeID equals dbEmployees.EmployeeID
group new
{
dbEmployees,
dbCategories,
dbOrderDetails
} by new
{
dbEmployees.FirstName,
dbEmployees.LastName,
dbCategories.CategoryName
} into gp
orderby gp.Sum(x => x.dbOrderDetails.Quantity) descending
select new
{
Employee = gp.Key.FirstName + " " + gp.Key.LastName,
Category = gp.Key.CategoryName,
Total = gp.Sum(x => x.dbOrderDetails.Quantity)
};
dgvTest.DataSource = sorgu16.ToList();
}
private void Form1_Load(object sender, EventArgs e)
{
// tüm ürünleri listeleyelim
NorthwindSabahEntities db = new NorthwindSabahEntities();
// product üzerinden ilerlendiği için tüm productlar gözükmeli yani tüm productlar gözükmeli yani left join
var sorgu1 = db.Products
.Select(x => new
{
x.ProductName,
x.UnitPrice,
x.Category.CategoryName
})
.ToList();
//dgvTest.DataSource = sorgu1;
// burada ilişki kurulduğu için ilşişkideki kurala göre inner join çalışır
var sorgu2 = from p in db.Products
join c in db.Categories on p.CategoryID equals c.CategoryID
select new
{
UrunAd = p.ProductName,
UrunFiyat = p.UnitPrice,
UrunKategori = c.CategoryName
};
//dgvTest.DataSource = sorgu2.ToList();
// çalışanlarımı email adresleri ile listeleyelim
var sorgu3 = db.Employees
.Select(x => new
{
x.FirstName,
x.LastName,
Email = (x.FirstName.Substring(0, 1) + x.LastName + "@northwind.com").ToLower()
}).ToList();
//dgvTest.DataSource = sorgu3;
var sorgu4 = from emp in db.Employees
select new
{
emp.FirstName,
emp.LastName,
Email = (emp.FirstName.Substring(0, 1) + emp.LastName + "@northwind.com").ToLower()
};
//dgvTest.DataSource = sorgu4.ToList();
this.Text = $"{db.Products.Average(x => x.UnitPrice):c2}";
var sorgu5 = db.Products // lambda expression
.Where(x => x.UnitPrice >= db.Products.Average(y => y.UnitPrice))
.Select(x => new
{
x.ProductName,
Fiyat = x.UnitPrice,
x.Category.CategoryName
})
.OrderByDescending(x => x.Fiyat)
.ToList();
//dgvTest.DataSource = sorgu5;
var sorgu6 = from p in db.Products // linq
where p.UnitPrice >= db.Products.Average(x => x.UnitPrice)
orderby p.UnitPrice descending
select new
{
p.ProductName,
Fiyat = p.UnitPrice,
p.Category.CategoryName
};
//dgvTest.DataSource = sorgu6.ToList();
// hangi kategoriden kaç tane ürünüm var
var sorgu7 = db.Products
.Where(x => x.CategoryID.HasValue && x.SupplierID.HasValue)
//.GroupBy(x => x.Category.CategoryName)
.GroupBy(x => new { x.Category.CategoryName, x.Supplier.CompanyName })
.Select(x => new
{
//CategoryName = x.Key,
CategoryName = x.Key.CategoryName,
CompanyName = x.Key.CompanyName,
Total = x.Count()
})
.OrderBy(x => x.CategoryName)
.ThenBy(x => x.CompanyName)
.ToList();
//dgvTest.DataSource = sorgu7;
var sorgu8 = from p in db.Products
join c in db.Categories on p.CategoryID equals c.CategoryID
join s in db.Suppliers on p.SupplierID equals s.SupplierID
group new
{
c,
s
}
by new
{
c.CategoryName,
s.CompanyName
} into gp
orderby gp.Key.CategoryName ascending, gp.Key.CategoryName ascending
select new
{
CategoryName = gp.Key.CategoryName,
CompanyName = gp.Key.CompanyName,
Total = gp.Count()
};
//dgvTest.DataSource = sorgu8.ToList();
// hangi üründen ne kadarlık sipariş verilmiş(tl bazında)
//var sorgu9 = db.Products
// .Join(db.Order_Details,
// p => p.ProductID,
// od => od.ProductID,
// (p, od) => new { p, od })
// .GroupBy(x => x.p.ProductName)
// .OrderBy(x => x.Key)
// .ToList()
// .Select(x => new
// {
// x.Key,
// Total = Math.Round(x.Sum(y => y.od.UnitPrice * y.od.Quantity * Convert.ToDecimal(1 - y.od.Discount)), 2)
// });
//dgvTest.DataSource = sorgu9.ToList();
var sorgu9 = db.Order_Details
.Join(db.Products,
od => od.ProductID,
product => product.ProductID,
(od, product) => new { od, product })
.GroupBy(x => x.product.ProductName)
.OrderBy(x => x.Key)
.ToList()
.Select(x => new
{
x.Key,
Total = Math.Round(x.Sum(y => y.od.UnitPrice * y.od.Quantity * Convert.ToDecimal(1 - y.od.Discount)), 2)
});
//dgvTest.DataSource = sorgu9.ToList();
//var sorgu10 = from p in db.Products
// join od in db.Order_Details on p.ProductID equals od.ProductID
// group new
// {
// p,
// od
// }
// by new
// {
// p.ProductName
// }
// into gp
// orderby gp.Key.ProductName
// select new
// {
// gp.Key.ProductName,
// Total = gp.Sum(x => x.od.UnitPrice * x.od.Quantity*Convert.ToDecimal(1-x.od.Discount))
// };
//dgvTest.DataSource = sorgu10.ToList();
var sorgu10 = from prod in db.Products
join od in db.Order_Details on prod.ProductID equals od.ProductID
group new
{
prod,
od
} by new
{
prod.ProductName
}
into gp
orderby gp.Key.ProductName
select new
{
gp.Key.ProductName,
Total = gp.Sum(x => x.od.UnitPrice * x.od.Quantity)
};
//dgvTest.DataSource = sorgu10.ToList();
// çalışanlarım kaç tane sipariş almış
//select e.EmployeeID,e.FirstName,sum(od.Quantity) from Employees e
//inner join Orders o on e.EmployeeID = o.EmployeeID
//inner join [Order Details] od on o.OrderID = od.OrderID
//group by e.EmployeeID,e.FirstName
var sorgu11 = from emp in db.Employees
join o in db.Orders on emp.EmployeeID equals o.EmployeeID
join od in db.Order_Details on o.OrderID equals od.OrderID
group new
{
emp,
//o,
od
} by new
{
emp.EmployeeID,
emp.FirstName
} into gp
orderby gp.Key.EmployeeID, gp.Key.FirstName
select new
{
gp.Key.EmployeeID,
gp.Key.FirstName,
Total = gp.Sum(x => x.od.Quantity)
};
//dgvTest.DataSource = sorgu11.ToList();
//var sorgu 12=db.Order_Details
// .Join(db.or)
// hangi kategoriden toplam kaç adet siparişim var
//select p.ProductName,sum(od.Quantity) as Toplam from Categories c
//join Products p on c.CategoryID = p.CategoryID
//join [Order Details] od on od.ProductID = p.ProductID
//group by p.ProductName
//order by Toplam
var sorgu13 = from c in db.Categories
join p in db.Products on c.CategoryID equals p.CategoryID
join od in db.Order_Details on p.ProductID equals od.ProductID
group new {
p,
c, od
} by new {
p.ProductName,
c.CategoryName
} into gp
//orderby gp.Key.ProductName
select new {
gp.Key.ProductName,
gp.Key.CategoryName,
Total = gp.Sum(x => x.od.Quantity)
};
//dgvTest.DataSource = sorgu13.OrderByDescending(x=>x.Total).ToList();
// sipariş no - toplam sipariş tutarı
// select many, groupjoin
// çalışanlarım hangi kategoriden kaç tane sipariş vermiş
//select e.EmployeeID,e.FirstName,c.CategoryName,sum(od.Quantity) as toplam from Employees e
//join Orders o on e.EmployeeID = o.EmployeeID
//join [Order Details] od on o.OrderID = od.OrderID
//join Products p on p.ProductID = od.ProductID
//join Categories c on p.CategoryID = c.CategoryID
//group by e.EmployeeID,e.FirstName,c.CategoryName
//order by toplam
var sorgu17 = from dbEmp in db.Employees
join dbO in db.Orders on dbEmp.EmployeeID equals dbO.EmployeeID
join dbOd in db.Order_Details on dbO.OrderID equals dbOd.OrderID
join dbP in db.Products on dbOd.ProductID equals dbP.ProductID
join dbC in db.Categories on dbP.CategoryID equals dbC.CategoryID
group new
{
dbEmp,
dbC,
//dbP,
dbOd
} by new
{
//dbEmp.EmployeeID,
dbEmp.FirstName,
dbC.CategoryName,
//dbOd.Quantity
} into gp
select new
{
//gp.Key.EmployeeID,
gp.Key.FirstName,
gp.Key.CategoryName,
Total = gp.Sum(x => x.dbOd.Quantity)
};
dgvTest.DataSource = sorgu17.OrderByDescending(x => x.Total).ToList();
}
