locked
How to select other fields when using Group By some fields RRS feed

  • Question

  • I have the following query in SQL:

    SELECT month(o.date)month, year(o.date)Year, Count(distinct o.user_name) count, o.group_name
    FROM operation o 
    GROUP BY month(o.date), year(o.date), o.group_name
    order by year, month

    I need to convert it to linq query. 

    but when I do Group in linq query by month, year and group_name, I cant select user_name because its not include in the 'group by fields'

    var result = (from var in RSE.Operations
    group var by new {var.Date.month,var.Date.year 
    into g
    orderby g.key.year, g.key.month, g.key.group_naeme
    select 
    (new
    {Number = '//My problem. I can not see g.key.user_name'
    Year = g.key.year
    Month = g.key.Month
    GroupName = g.key.Group_Name
    })):

    How can I do it?

    Thanks.

    Wednesday, January 30, 2013 8:05 AM

Answers

  • Hi rgr123;

    I believe that the following query should do what you need.

    var result = (from var in RSE.Operations
                  group var by new {var.Date.month, var.Date.year, var.group_name } into g
                  orderby g.key.year, g.key.month
                  select new {
                      Number = (from u in g
                                select u.user_name).Distinct().Count(),
                      Year = g.key.year,
                      Month = g.key.Month,
                      GroupName = g.key.Group_Name
                  });  

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by rgr123 Thursday, January 31, 2013 11:56 AM
    Wednesday, January 30, 2013 3:59 PM