none
Group By Date (but ignoring the time portion)

    Question

  • Something tells me this should be easier than I think.

     

    from i in ADB.ListItem

    group i by i.EffectiveDate into g

    select g

     

    but with dates like 5/10/2008 06:33:11 the grouping doenst work right.

     

    I need it to group by the Date portion of the date & ignore the time portion, I thought maybe:

     

    from i in ADB.ListItem

    group i by i.EffectiveDate.Date into g

    select g

     

    But that errors out as Linq to Entities doesn't know about .Date.

     

    What is the trick?

     

    Thanks!

    Tuesday, May 13, 2008 9:16 PM

Answers

  •  

    Hi Ryan,

     

    Another option is to do:

     

    Code Snippet

    var q = from i in ABD.Listitem

            let dt = p.EffectiveDate

            group i by new { y = dt.Year, m = dt.Month, d = dt.Day}

            select g;

     

    Thanks,

    Kati

    Wednesday, May 14, 2008 12:08 AM
    Moderator

All replies

  • Hi Ryan,

     

    Since there is no function we support to extract the Date portion of a DateTime, you can use the trick explained in this other thread:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3274604&SiteID=1

     

    Basically, it is about creating a function from DateTime into Int that preserves the order and also give you distinct values for the cases that you care about.

     

    Code Snippet

    var q = from i in ABD.Listitem

            let d = p.EffectiveDate

            group i by (d.Year - 2000) * 384 + d.Month * 32 + d.Day into g

            select g;

     

    I don't have your model, and so I have just compiled this query in my head Smile. Let me know if it works for you. Also, I know this doesn't look pretty. We plan expanding LINQ to Entities to support translating more constructs in future versions.

     

    Hope this helps,

    -Diego

    Tuesday, May 13, 2008 11:20 PM
    Moderator
  •  

    Hi Ryan,

     

    Another option is to do:

     

    Code Snippet

    var q = from i in ABD.Listitem

            let dt = p.EffectiveDate

            group i by new { y = dt.Year, m = dt.Month, d = dt.Day}

            select g;

     

    Thanks,

    Kati

    Wednesday, May 14, 2008 12:08 AM
    Moderator
  • I think what Kati suggests is actually the exact thing you should do in this case.

     

    I often forget the fact that you can use this anonymous type syntax to define your group key in LINQ.

     

    The solution based on the monotonic function I proposed is more useful when you actually want to do other kind of comparisons like inequalities. You don't need it to do a GROUP BY.

     

    Other than that, I actually tried to compile the code this time and I found a few things to fix Smile

     

    Code Snippet

    var q = from i in ABD.Listitem

            let dt = i.EffectiveDate

            group i by new { y = dt.Year, m = dt.Month, d = dt.Day } into g

            select g;

     

     

    -Diego
    Wednesday, May 14, 2008 12:21 AM
    Moderator
  •  

    Thank you both, both solutions worked actually.

     

    I am using the second, but it was nice to think about how the first worked,

     

    Ryan

    Wednesday, May 14, 2008 12:23 AM
  • Code Snippet

    var q = from i in ABD.Listitem

            let d = p.EffectiveDate

            group i by (d.Year - 2000) * 384 + d.Month * 32 + d.Day into g

            select g;

    Why 384 * year ??
    why 32 * Month ?...

    Instead you should simply toString it all : ""  + d.Year + d.Month + d.Day (maybe including a separator)
    The anonymous Key is the better of the two solutions, but your first example would yield a Key that couldn't be used afterwords.


    Thursday, November 10, 2011 9:07 AM
  • Erm ...

    2011-1-23 -> 2011123

    2011-12-3 -> 2011132

    Plus ... does this actually work? Seems you are using VB.Net that issues a ToString() on the numbers returned by d.Year automatically, but ToString() is not supported by LINQ to Entities. I don't want to touch VB so I can't test it, but I do believe

      group i by d.Year.ToString() + "-" + d.Month.ToString() + "-" + d.Day.ToString() 

    in C# would compile, but not run.

     

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Thursday, November 10, 2011 1:28 PM
  • Yea... hehe... I was tinking in zeropadded date and month -> 20110102

    but you are maybe right, that the toString() fails in Entity Framework... 

    Thursday, November 10, 2011 2:06 PM
  • var q = from i in ABD.Listitem

            group i by i.SomeDateTime.Date into g

            select g;

    http://msdn.microsoft.com/en-us/library/system.datetime.date.aspx


    public DateTime Date { get; }


    Gets the date component of this instance.

    Tuesday, March 27, 2012 3:03 PM