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<DatewiseStockDetailViewModel> 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<DatewiseStockDetailViewModel>();
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