Converting Sql Query to Linq

Neerav

8y2.1kLINQ

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Neerav8y ago
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 < sub.Count; i++)

{

objDataColumn.Add(sub[i]);

}

}

//Add dynamic columns name to datatable dt

for (int i = 0; i < objDataColumn.Count; i++)

{

dt.Columns.Add(objDataColumn[i].ToString());

}

for (int i = 0; i < d.Count; 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 < res.Count; 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;

}
Neerav8y ago
Dear sir,

I tried removing Id but the result is the same. and yes the product id is unique
Piyush Pansuriya8y ago
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{}