4

I am trying to convert the following SQL into a LINQ expression

SELECT    COUNT(ID) AS Count, MyCode
FROM      dbo.Archive
WHERE     DateSent>=@DateStartMonth AND DateSent<=@DateEndMonth
GROUP BY  MyCode

and I have been trying to follow this webpage as an example:
Converting SQL containing top, count, group and order to LINQ (2 Entities)

I got this so far but I am stuck on understanding the new part

var res = (from p in db.Archives
           where (p.DateSent>= dateStartMonth) && (p.DateSent< dateToday)
           group p by p.MyCode into g
           select new { ??????MyCode = g.something?, MonthlyCount= g.Count() });

Thanks in advance for helping


UPDATE:
can you explain what g.Key is? I dont understand where that variable came from or what it is referring too? I mean what if I group on 4 different things? How would I refer to each one?

var res = from archive in db.Archives
      where archive.DateSent >= dateStartMonth &&
            archive.DateSent < dateToday
      group archive by archive.MyCode, archive.Extra into archiveGrp
      select new 
      { 
           MyCode = archiveGrp.Key, 
           Extra = archiveGrp.???
           MonthlyCount = archiveGrp.Count() 
      };
Community
  • 1
  • 1
Philip
  • 579
  • 3
  • 8
  • 19

2 Answers2

6

The LINQ statement below should work:

var res = from archive in db.Archives
          where archive.DateSent >= dateStartMonth &&
                archive.DateSent < dateToday
          group archive by archive.MyCode into archiveGrp
          select new 
          { 
               MyCode = archiveGrp.Key, 
               MonthlyCount = archiveGrp.Count() 
          };

Notice that the Key property will contain the value of the property that you group on, in this case MyCode.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
Yvo
  • 18,681
  • 11
  • 71
  • 90
0
from p in archive
where p.DateSent >= dateStartMonth && p.DateSent < dateToday
group p by p.MyCode into g
select new { Count = g.Count(), MyCode = g.Key  }

produces the same output as your Sql in Linqpad

Nicholas Murray
  • 13,305
  • 14
  • 65
  • 84