SQL command to LINQ (pivoting)

Question

Hello!

I'm moving DB from MySQL (used ODBC) to MS SQL and I want to "translate" SQL queries to LINQ. Can someone help me with this (it should SUM Charge column for every location and group result by months):

SELECT
sum(case when Location="Location1" then Charge else 0 end) as Location1,
sum(case when Location="Location2" then Charge else 0 end) as Location2,
sum(case when Location="Location3" then Charge else 0 end) as Location3,
MAKEDATE(YEAR(OrderTime),DAYOFYEAR(OrderTime)) AS date FROM Sales
GROUP BY YEAR(OrderTime),MONTH(OrderTime)
ORDER BY OrderTime DESC

?

Output should look like this:

Location1 | Location2 | Location3 | date

Thanks in advance

EDIT:

I tryed to use LINQ sample from here:

http://stackoverflow.com/questions/167304/is-it-possible-to-pivot-data-using-linq

var query = context.log_sales
          .GroupBy(c => c.OrderTime)
          .Select(g => new
          {
                                Date = g.Key,
           Location1 = g.Where(c => c.Location == "Location1").Sum(c => c.Charge) ?? 0,
           Location2 = g.Where(c => c.Location == "Location2").Sum(c => c.Charge) ?? 0
          }).ToList();

and it is almost what I need. There should be grouping by year too and I don't know how to do this.

Answer 1

This might help.

context.log_sales
.GroupBy(s => new {Year = OrderTime.Year, Month = OrderTime.Month})
.Select
( g => new {
  Date = new DateTime(g.Key.Year, g.Key.Month, 1),
  Location1 = g.Where(s => s.Location == "Location1").Sum(s => s.Charge),
  Location2 = g.Where(s => s.Location == "Location2").Sum(s => s.Charge),
  Location3 = g.Where(s => s.Location == "Location3").Sum(s => s.Charge),
  }
)
.OrderBy(x => x.Date);

Answer 2

This is exactly what I needed. Big thanks

Answer 3

.. do You know maybe how to make adding Locations to Select dynamically? For example from List/Array.

EDIT: .. or maybe making Locations to load dynamically and setting which Locations should be loaded in Where statement before Select. Is this possible?