Show record using entity framework or linq

Question

I have record

voucher_id    voucher_Date    Voucher_type     Total

1              1/2/2015        sale            10000

2              1/2/2015        sale            15000

3              1/2/2015       purchase         5000

4              25/2/2015       sale            10000

how to display data in date wise and calculate as,

Voucher_Date     Sale         Purchase

1/2/2015          25000        5000

25/2/2015         10000

Answer 1

The query should be something like:

var res = from x in MyTable
          group x by x.voucher_Date into y
          select new 
          { 
              Voucher_Date = y.Voucher_Date, 
              Sale = y.Sum(z => z.Voucher_type == "sale" ? z.Total : 0),
              Purchase = y.Sum(z => z.Voucher_type == "purchase" ? z.Total : 0),
          }

Note that I'm not sure if EF supports the form of Sum I have used.

The equivalent SQL query should be:

SELECT Voucher_Date,
       SUM(CASE WHEN Voucher_type = 'sale' THEN Total ELSE 0 END) Sale,
       SUM(CASE WHEN Voucher_type = 'purchase' THEN Total ELSE 0 END) Purchase
       FROM MyTable
       GROUP BY voucher_Date