none
Aggregation multiple columns with group by RRS feed

  • Question

  • Hi,

    I'm a Linq rookie. I'd like to use Linq to query a table. In TSQL I write this:

    select

     

    distinct
    sum(Col1) as Col1,
    sum(Col2) as Col2,
    sum(Col3) as Col3,
    sum(Col4) as Col4
    from myTable where date>= '01.01.2008 00:00:00' and date<= '01.02.2008 00:00:00' and kriteria1 = '3' and kriteria2 = '1' and kriteria3 = '1' group by id, kritera1, date, kriteria2, kriteria3

    This returns one row as aspected. How can I achieve this result in Linq?
    Many thanks!

    Sunday, May 10, 2009 7:29 PM

Answers

  • Sorry - what was I thinking!

    You'll need to change the identifier in the lambda exressions at the end to something else:


    from m in db.myTables
      where m.Date >= new DateTime (2008, 1, 1) && m.Date <= new DateTime (2008, 1, 2)
      where m.kriteria1 == '3' && m.kriteria2 == '1' && m.kriteria3 == '1'
      group m by new { m.kriteria1, m.kriteria2, m.kriteria3  } into g
      select new
      {
         Col1 = g.Sum (x => x.Col1),
         Col2 = g.Sum (x => x.Col2),
         Col3 = g.Sum (x => x.Col3),
         Col4 = g.Sum (x => x.Col4),
      }
    Write LINQ queries interactively - www.linqpad.net
    Tuesday, May 12, 2009 1:44 AM
    Answerer

All replies

  • The same query in LINQ to SQL is this:


      from m in db.myTables
      where m.Date >= new DateTime (2008, 1, 1) && m.Date <= new DateTime (2008, 1, 2)
      where m.kriteria1 == '3' && m.kriteria2 == '1' && m.kriteria3 == '1'
      group m by new { m.kriteria1, m.kriteria2, m.kriteria3  } into g
      select new
      {
         Col1 = g.Sum (g => g.Col1),
         Col2 = g.Sum (g => g.Col2),
         Col3 = g.Sum (g => g.Col3),
         Col4 = g.Sum (g => g.Col4),
      }


    Joe

    Write LINQ queries interactively - www.linqpad.net
    Monday, May 11, 2009 1:34 AM
    Answerer
  • Hi Joe,

    thank you for this. I've tried it but get this compiler error:

    A local variable named 'g' cannot be declared in this scope because it would give a different meaning to 'g', which is already used in a 'parent or current' scope to denote something else
    Monday, May 11, 2009 7:24 PM
  • Sorry - what was I thinking!

    You'll need to change the identifier in the lambda exressions at the end to something else:


    from m in db.myTables
      where m.Date >= new DateTime (2008, 1, 1) && m.Date <= new DateTime (2008, 1, 2)
      where m.kriteria1 == '3' && m.kriteria2 == '1' && m.kriteria3 == '1'
      group m by new { m.kriteria1, m.kriteria2, m.kriteria3  } into g
      select new
      {
         Col1 = g.Sum (x => x.Col1),
         Col2 = g.Sum (x => x.Col2),
         Col3 = g.Sum (x => x.Col3),
         Col4 = g.Sum (x => x.Col4),
      }
    Write LINQ queries interactively - www.linqpad.net
    Tuesday, May 12, 2009 1:44 AM
    Answerer