SQL command to LINQ (pivoting)

Question

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

EDIT:

I tryed to use LINQ sample from here:

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.

Working on an answer... just wanted to mention that the ORDER BY in the sql is not valid. OrderTime was grouped away and can't be used there. — Amy B, Oct 08 '08 at 18:00

score 6 · Accepted Answer · answered Oct 08 '08 at 18:03

6

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

answered Oct 08 '08 at 18:03

Amy B

108,202
21
135
185

Have You checked what SQL is generating this Linq expression? It's quite HUGE. – GrZeCh Oct 09 '08 at 09:37

score 0 · Answer 2 · answered Oct 09 '08 at 08:43

.. 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?

SQL command to LINQ (pivoting)

2 Answers2

Linked