none
How to do some GROUPING SETS in Linq To Sql, please? RRS feed

  • Question

  • Hi folks,

       i have the following sql code that works fine (based upon sql 2008 online help, using adventure works db).. How can i replicate this into linq? i've never heard of the GROUPING SETS key word(s) before...

     

    SELECT COUNT(ReviewId) AS 'Total Reviews'

    ,DATEPART(year,DateCreated)AS 'By Year'

    ,DATEPART(month,DateCreated) AS 'By Month'

    ,DATEPART(week,DateCreated) AS 'By Week'

    FROM Reviews

    GROUP BY GROUPING SETS

    (

    (

    DATEPART(year,DateCreated)

    ,DATEPART(month,DateCreated),

    DATEPART(week,DateCreated)

    )

    )

    ORDER BY DATEPART(year,DateCreated)

    ,DATEPART(month,DateCreated)

    ,DATEPART(week,DateCreated)

    Thursday, May 15, 2008 8:04 AM

Answers

  • This is not pretty, but I hope this will work for you:

     

    Code Snippet

    var query = from r in Orders

                let date = r.OrderDate.Value

                group r by new

                {

                    date.Year,

                    date.Month,

                    Week =

    (((date.DayOfYear - 1) + (((((int)date.DayOfWeek) - ((date.DayOfYear - 1) % 7)) + 14) % 7)) / 7) + 1

     

                } into g

                orderby g.Key.Year, g.Key.Month, g.Key.Week

                select new

                {

                    OrderCount = g.Count(),

                    DateTime = System.Globalization.CultureInfo.InvariantCulture.Calendar.AddWeeks(new DateTime(g.Key.Year, 1, 1), g.Key.Week)

                              

                };

                        

    var results = query.ToList();

     

     

    I haven't tried, but perhaps it is possible to simplify the expression further.

    - Diego 

    Friday, May 16, 2008 10:08 PM

All replies

  • Since your query involves only a single grouping set, it can be replicated using an ordinary group by function:

     

    Code Snippet

    var query = from r in db.Revies

    group r by new

    {

    r.DateCreated.Year,

    r.DateCreated.Month,

    r.DateCreated.Week

    } into g

    orderby g.Key.Year, g.Key.Month, g.Key.Week

    select new

    {

    TotalReviews = g.Count(rev => rev.ReviewID),

    ByYear = g.Key.Year,

    ByMonth = g.Key.Month,

    ByWeek = g.Key.Week

    }

     

     

     

     

    If you had to deal with multiple grouping sets, you could always use the Concat extension method from your query result, and write an additional query for each additional grouping set.

    Thursday, May 15, 2008 2:32 PM
  • Hi Martin.

       thanks for the prompt reply. I tried what you did but hit a stumbling block -> there is no WEEK property on a DateTime object.

     

    so i tried the following .. it compiles .. but the Linq fails .. saying that the GetWeekOfYear method has no supported translation to SQL. Sad

     

     

    Code Snippet

    resultList = (from r in db.Reviews

    group r by new

    {

        r.DateCreated.Year,

        r.DateCreated.Month,

        Week = GlobalCultureInfo.InvariantCulture.Calendar.GetWeekOfYear(r.DateCreated,

            GlobalCultureInfo.InvariantCulture.DateTimeFormat.CalendarWeekRule,

            GlobalCultureInfo.InvariantCulture.DateTimeFormat.FirstDayOfWeek)

    } into g

    orderby g.Key.Year, g.Key.Month, g.Key.Week

    select new

    {

        ReviewCount = g.Count(rc => rc.ReviewId > 0),

        DateTime = GlobalCultureInfo.InvariantCulture.Calendar.AddWeeks(new DateTime(g.Key.Year, 1, 1),

            g.Key.Week)

    }).ToList();

     

     

    suggestions?

    Friday, May 16, 2008 1:45 AM
  • I'm not sure if it will work, but you can always try to run a custom math operation that provides you with the week:

     

    Week = (r.DateCreated.DayOfYear - r.DateCreated.DayOfWeek) / 7 + 1

    Friday, May 16, 2008 7:37 PM
  • This is not pretty, but I hope this will work for you:

     

    Code Snippet

    var query = from r in Orders

                let date = r.OrderDate.Value

                group r by new

                {

                    date.Year,

                    date.Month,

                    Week =

    (((date.DayOfYear - 1) + (((((int)date.DayOfWeek) - ((date.DayOfYear - 1) % 7)) + 14) % 7)) / 7) + 1

     

                } into g

                orderby g.Key.Year, g.Key.Month, g.Key.Week

                select new

                {

                    OrderCount = g.Count(),

                    DateTime = System.Globalization.CultureInfo.InvariantCulture.Calendar.AddWeeks(new DateTime(g.Key.Year, 1, 1), g.Key.Week)

                              

                };

                        

    var results = query.ToList();

     

     

    I haven't tried, but perhaps it is possible to simplify the expression further.

    - Diego 

    Friday, May 16, 2008 10:08 PM
  • I am going to mark the previous post as the correct answer, but feel free to unmark it if it did not help.

    Thanks,
    Diego

    Monday, May 26, 2008 3:18 PM
  • Thanks Diego. It works great .. i forgot to mark it.

     

    Good work and thanks!

     

    (/me leaving it answered).

    Monday, May 26, 2008 11:55 PM
  • I used your solution thanks Diego

     

    for what it is worth I did mine with a 3 table join, a single criteria and sort then used the result to poulate a treeview

    I am sure it could be improved but here it is for anyone stuck like I was

     

    SqlConnection dBConn = new SqlConnection();

    dBConn = dw.Make_dBConnSQLExpress("MPC");

    MPCLinqDataContext db = new MPCLinqDataContext(dBConn);

    tvw.Nodes.Clear();

    // 2 joins, sort and multiple group

    var Rec = from rt in db.TblRTypes

    join rids in db.tblRecipes on rt.RtypeId equals rids.RtypeId

    join did in db.TblDishItems on rids.RId equals did.RId

    where rids.ConceptID == cId

    group rt by new

    {

    rt.RtypeId,

    rt.RType

    }

    into grp

    orderby grp.Key.RType

    select new

    {

    id = grp.Key.RtypeId,

    Type = grp.Key.RType

    };

    foreach (var r in Rec)

    {

    TreeNode node = new TreeNode(r.Type, 3, 3);

    node.Nodes.Add("");

    node.Tag = r.id;

    tvw.Nodes.Add(node);

    tvw.SelectedNode = node;

     

    Monday, July 28, 2008 4:31 PM
  • Hi Martin,

    unfortunately,
    this formula cannot be used for ISO weeks (http://en.wikipedia.org/wiki/ISO_week_date)
    where the first week of the year must have a thursday.

    Example:
    1.January 2011 will be in the 52th week of 2010


    Wednesday, July 8, 2009 1:57 PM