1

I am using Entity Framework in my sample application. It has two DbSets TICKETS and DEPARTMENT which correspond to TICKETS and DEPARTMENT sql server tables.

I would like to write LINQ query which would result in below sql query or produce output similar to it.

SELECT   D.NAME as DeptName, T.STATUS as Status, COUNT(1) as Count
FROM     TICKETS T
      , DEPARTMENT D 
WHERE    T.FK_DEPT_ID = D.PK_DEPT_ID
GROUP BY D.NAME , T.STATUS;
Wesley Lomax
  • 2,067
  • 2
  • 20
  • 34
refactor
  • 13,954
  • 24
  • 68
  • 103
  • you could use something like [linqpad](https://www.linqpad.net/) to test/generate your linq query. See this [answer](http://stackoverflow.com/questions/12238423/linqpad-convert-sql-to-linq-command) for more help – Zippy Oct 07 '15 at 08:38
  • What have you tried and where are you stuck? Besides: Is there any reason why you don't use a join? – Marco Oct 07 '15 at 08:45
  • @serv , I have used LINQ joins , but was not able to get the desired output. – refactor Oct 07 '15 at 08:52
  • I primarily meant in your SQL query – Marco Oct 07 '15 at 08:54

2 Answers2

2

You should be able to use following LINQ-Query to achieve your result:

var result = from t in conn.TICKETS
             join dept in conn.DEPARTMENT on t.FK_DEPT_ID equals dept.PK_DEPT_ID
             select new { DeptName = dept.NAME, Status = t.STATUS }
             into temp
             group temp by new { temp.DeptName, temp.Status }
             into g
             select new { g.Key.DeptName, g.Key.Status, Count = g.Count()};
Michael Mairegger
  • 6,833
  • 28
  • 41
1

You can use this method syntax too.

db.Tickets.Join(db.Department,t=>t.FK_DEPT_ID,d=>d.PK_DEPT_ID,(x,y)=>new{x.STATUS,y.NAME})
          .GroupBy(g=>new {g.STATUS,g.NAME})
          .Select(z=>new {z.Key.Name,z.key.STATUS,z.Count()})
Sateesh Pagolu
  • 9,282
  • 2
  • 30
  • 48