none
Do a count in one single table RRS feed

  • Question

  • Table ClientCalls:

    CallId: int, ClientName: string, CallDate: Date, isActive: bit

    in plain sql I would do this:

    select ClientName, count(CallId) as Count
    from ClientCalls
    where CallDate between '01/01/2009' and '01/31/2009 23:59:59' and isActive = true
    group by ClientName
    I know it's not the right structure but this is what I have for the moment that I need to deal with, unfortunately. How can represent this using Linq please?

    Thanks
    • Edited by Fahd Friday, June 12, 2009 7:14 PM
    Friday, June 12, 2009 5:49 PM

Answers

  •    from c in ClientCalls
       where c.CallDate >= startDate && c.CallDate <= endDate && isActive
       group c by c.ClientName into g
       select new { ClientName = g.Key, Count = g.Count() }


    Wayward LINQ Lacky
    • Marked as answer by Fahd Friday, June 12, 2009 6:36 PM
    • Unmarked as answer by Fahd Friday, June 12, 2009 7:14 PM
    • Proposed as answer by CetinBasoz Friday, June 12, 2009 7:59 PM
    • Marked as answer by Fahd Friday, June 12, 2009 8:17 PM
    Friday, June 12, 2009 6:23 PM
    Moderator

All replies

  •    from c in ClientCalls
       where c.CallDate >= startDate && c.CallDate <= endDate && isActive
       group c by c.ClientName into g
       select new { ClientName = g.Key, Count = g.Count() }


    Wayward LINQ Lacky
    • Marked as answer by Fahd Friday, June 12, 2009 6:36 PM
    • Unmarked as answer by Fahd Friday, June 12, 2009 7:14 PM
    • Proposed as answer by CetinBasoz Friday, June 12, 2009 7:59 PM
    • Marked as answer by Fahd Friday, June 12, 2009 8:17 PM
    Friday, June 12, 2009 6:23 PM
    Moderator
  • that worked, thank you. However, I still don't know how to approach this from an Nhibernate experience. What I am returning is new type that I need to create from the var object.
    is there a better way of doing? meaning can I do this:

    CallReport  callReport = (from c in ClientCalls
                     where c.CallDate >= startDate && c.CallDate <= endDate && isActive
                     group c by c.ClientName into g
                     select new { ClientName = g.Key, Count = g.Count() }).ToList<CallReport>();


    Knowing CallReport represent only what coming out of the result.

    OR

    this :

    var calls = from c in ClientCalls
       where c.CallDate >= startDate && c.CallDate <= endDate && isActive
       group c by c.ClientName into g
       select new { ClientName = g.Key, Count = g.Count() }
    
    foreach...

    Friday, June 12, 2009 6:42 PM
  • Your query didn't work because what you are actually counting is different client name and not how many records for each client name. I thought NHibernate was difficult but this my goodness, to be honest, it's far difficult to do a simple thing.

    Friday, June 12, 2009 7:16 PM
  •    from c in ClientCalls
       where c.CallDate >= startDate && c.CallDate <= endDate && isActive
       group c by c.ClientName into g
       select new { ClientName = g.Key, Count = g.Count() }


    Wayward LINQ Lacky

    Just a slight correction - it would be c.isActive
    Friday, June 12, 2009 8:01 PM
  • It is counting how many records for each client. In other words the Linq Matt provided is based on SQL you provided. If you check the SQL generated it's what you wanted. Maybe your SQL was wrong and you meant something else.
    Friday, June 12, 2009 8:04 PM
  • You're right, I was wrong and I apologize. It did in fact return the correct count.
    Friday, June 12, 2009 8:23 PM