Converting Sql Query to Linq

Question

Hello Friends, I have a chart which i have built using the linq query which is as below public List getProductSoldByYear( int CompanyId) { var userData = (from m in datewise.GetAll() join s in stock.GetAll() on m.Stock_Id equals s.Stock_Id join p in product.GetAll() on s.ProductId equals p.ProductId where s.CompanyId == CompanyId && m.IsProductDeducted == true group new { m, s, p } by new { Convert.ToDateTime(m.CreatedDate.ToString()).Year, p.ProductName, p.ProductId } into g select new DatewiseStockDetailViewModel { productId = Convert.ToInt32(g.Key.ProductId), productName = g.Key.ProductName, ProductQuantity = g.Sum(x => x.m.ProductQuantity), year = g.Key.Year.ToString() }).Distinct().ToList (); return userData; } The Output for the same is But i want to write a code by which i am able to use the pivot code in linq to get the output as ProductName 2016 2017 2018 cedar oil NULL 16 34 computer NULL 8 1 grain NULL 21 NULL Keyboard 2 3 NULL marie NULL 26 NULL Pine Oil NULL 4 5 harabhara kebabNULL 5 1 For this i have written the sql query select * from ( select pd.ProductName, year (dsm.CreatedDate) as [ Year ], sum (dsm.ProductQuantity) as ProductQuantity from dbo.DatewiseStockDetailMaster dsm left join dbo.StockMaster sm on dsm.stock_Id=sm.stock_Id left join dbo.ProductDetails pd on sm.ProductId=pd.ProductId where sm.CompanyId=17 and dsm.IsProductDeducted=1 group by year (dsm.CreatedDate),pd.ProductName ) as SourceTable pivot ( sum (ProductQuantity) for [ Year ] in ([2016],[2017],[2018]) ) as pivotTable Can anyone help me convert this SQL to Linq

Piyush Pansuriya · Answer

Hello, As per your code for linq, You had select product id in select new DatewiseStockDetailViewModel { productId = Convert.ToInt32(g.Key.ProductId), productName = g.Key.ProductName, ProductQuantity = g.Sum(x => x.m.ProductQuantity), year = g.Key.Year.ToString() }).Distinct().ToList (); return userData; } So, it is unique id for all product ? if it will different by year wise, than remove that line from the select{}

Neerav · Answer

I got the work around. with the help of below mentioned link https://www.codeproject.com/Tips/844709/Pivot-Table-Using-Linq-Entity-and-SQL-Server-R-for and little bit of tweek try { DataTable dt = new DataTable(); var data = admin.getProductSoldByYear(cId); var d = (from f in data group f by new { f.productName } into myGroup where myGroup.Count() > 0 select new { myGroup.Key.productName, //myGroup.Key.year, //myGroup.Key.Total, subject = myGroup.GroupBy(f => f.year).Select (m => new { Sub = m.Key, Score = m.Sum(c => c.ProductQuantity) }) }).ToList(); var sub = admin.getYearforProducts(cId).Select(a => a.year).ToList(); //Creating array for adding dynamic columns ArrayList objDataColumn = new ArrayList(); if (data.Count() > 0) { //Three column are fix "rank","pupil","Total". objDataColumn.Add("productName"); //Add Subject Name as column in Datatable for (int i = 0; i { objDataColumn.Add(sub[i]); } } //Add dynamic columns name to datatable dt for (int i = 0; i { dt.Columns.Add(objDataColumn[i].ToString()); } for (int i = 0; i { DataRow dr = dt.NewRow(); dr[0] = d[i].productName.ToString(); var res = d[i].subject.ToList(); int cnt = 0; foreach (var item in dt.Columns) { for (int j = 0; j { if (item.ToString() == res[j].Sub) dr[cnt] = res[j].Score; } cnt++; } dt.Rows.Add(dr); } return View(dt); //Now the Pivot datatable return like below screen } catch (Exception) { return null; }

Neerav · Answer

Dear sir, I tried removing Id but the result is the same. and yes the product id is unique