none
How do I simply group a DateTime by DAY????

    Question

  • line2ent doesnt support ToShortDateTime() so that doesnt work.

     

    Should I create a partial class for the Entity that convert the DateTime into a int iDay???

     

    Ive been trying to find thw answer to this for days.. I am making some graphs in SL and need to group by the DAY using linq2ent. HELP

     

    I can't seem to find any god reference on this. I have alot of data in SQL with dates. So I wanted to make a line chart to show this data over time. If I want to show it over a perioud of days then I need to group by days.. But the LOGDATE is the full date.. not the DAY..

    So I have this below.. but LINQ doesnt know what 'DayOfYear' property is.. HELP

     var q = from x in dc.ApplicationLogs 
                    let dt = x.LogDate 
                    group x by new { dayofyear = dt.Value.DayOfYear } into g 
                    select new 
                    { 
                        iCount = g.Count(), 
                        strDate = g.Key 
                    }; 
    


    If you do SharePoint dev and have MSN add me!! punkouter@hotmail.com We can help each other out. My SharePoint Blog ---> http://punkouter.spaces.live.com/
    Friday, June 18, 2010 5:06 PM

Answers

  • DayOfYear is available as a canonical (EDM) function in the Entity Framework and can be used from LinqToEntites in the following way:

     

    1.       Create a proxy method DayOfYear:

            [EdmFunction("Edm", "DayOfYear")]

            public static System.Int32? DayOfYear(System.DateTime? timeValue1)

            {

                throw new NotSupportedException("This function should only be invoked from Linq To Entities");

            }

     

    2.       Use it:

    var q = from x in dc.ApplicationLogs 

                    group x by DayOfYear(x.LogDate) into

                    select new 

                    { 

                        iCount = g.Count(), 

                        strDate = g.Key 

                    };

     

    Typically, you don’t need to do this for canonical functions (they are available via the EntityFunctions class).  We will look into supporting DayOfYear either directly or via EntityFunctions as wll.

     

    Alternatively, you could group by {Year, Month, Day}, i.e:

    var q = from x in dc.ApplicationLogs 

                    group x by new { year = x.LogDate.Year, month=x.LogDate.Month, day = x.LogDate.day } into

                    select new 

                    { 

                        iCount = g.Count(), 

                        strDate = g.Key 

                    }; 

    Including the year in the grouping key may be something you want to do anyway.

    I hope this helps.

    Thanks,
    Kati


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 18, 2010 10:12 PM
    Moderator
  • Hello *,

    those are great options to choose from.  Another option that i would recommend is use of SqlFunctions class which exposes sql server functions in linq to entities. To write your above query, you can just use the function without any work from your side.

    var db = new NorthwindEFEntities();

    var query = from o in db.Orders

                group o by SqlFunctions.DatePart("dy",o.OrderDate) into g

                select new 

                {

                    DayOfTheYear = g.Key.Value,

                    Orders = g

                };

    foreach (var rec in query)

    {

        Console.WriteLine("Day:{0} Count:{1}",rec.DayOfTheYear,rec.Orders.Count());

    }

    If you want to learn more about database function you can look at 11-11 recipe in my book.



    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress http://weblogs.asp.net/zeeshanhirani
    Saturday, June 19, 2010 2:53 AM
  • thanks. I am also told this is another method

     

       var a = (from row in e.tests group row by new{dateKey=row.dt.Year+(row.dt.Month/12.0)+(row.dt.Day/31.0), actualDate=row.dt} into g select new { key = g.Key.dateKey, actualDate=g.Key.actualDate, count = g.Count() });


    If you do SharePoint dev and have MSN add me!! punkouter@hotmail.com We can help each other out. My SharePoint Blog ---> http://punkouter.spaces.live.com/
    Monday, June 21, 2010 3:34 PM

All replies

  • I don't get what you mean, but you don't need to use let clause, DayOfYear works for me even the column is DateTime? type

    var q =

    from x in db.ApplicationLogs

    group x by x.LogDate.Value.DayOfYear into g

    select new

    {

    iCount = g.Count(),

    strDate = g.Key

    };

    Friday, June 18, 2010 5:39 PM
  • cant do this

     

    +  base {"The specified type member 'DayOfYear' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."} System.SystemException {System.NotSupportedException}

     

     


    If you do SharePoint dev and have MSN add me!! punkouter@hotmail.com We can help each other out. My SharePoint Blog ---> http://punkouter.spaces.live.com/
    Friday, June 18, 2010 6:38 PM
  • ahh, maybe linq to sql is different from linq to entities...
    Friday, June 18, 2010 6:49 PM
  • DayOfYear is available as a canonical (EDM) function in the Entity Framework and can be used from LinqToEntites in the following way:

     

    1.       Create a proxy method DayOfYear:

            [EdmFunction("Edm", "DayOfYear")]

            public static System.Int32? DayOfYear(System.DateTime? timeValue1)

            {

                throw new NotSupportedException("This function should only be invoked from Linq To Entities");

            }

     

    2.       Use it:

    var q = from x in dc.ApplicationLogs 

                    group x by DayOfYear(x.LogDate) into

                    select new 

                    { 

                        iCount = g.Count(), 

                        strDate = g.Key 

                    };

     

    Typically, you don’t need to do this for canonical functions (they are available via the EntityFunctions class).  We will look into supporting DayOfYear either directly or via EntityFunctions as wll.

     

    Alternatively, you could group by {Year, Month, Day}, i.e:

    var q = from x in dc.ApplicationLogs 

                    group x by new { year = x.LogDate.Year, month=x.LogDate.Month, day = x.LogDate.day } into

                    select new 

                    { 

                        iCount = g.Count(), 

                        strDate = g.Key 

                    }; 

    Including the year in the grouping key may be something you want to do anyway.

    I hope this helps.

    Thanks,
    Kati


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 18, 2010 10:12 PM
    Moderator
  • Hello *,

    those are great options to choose from.  Another option that i would recommend is use of SqlFunctions class which exposes sql server functions in linq to entities. To write your above query, you can just use the function without any work from your side.

    var db = new NorthwindEFEntities();

    var query = from o in db.Orders

                group o by SqlFunctions.DatePart("dy",o.OrderDate) into g

                select new 

                {

                    DayOfTheYear = g.Key.Value,

                    Orders = g

                };

    foreach (var rec in query)

    {

        Console.WriteLine("Day:{0} Count:{1}",rec.DayOfTheYear,rec.Orders.Count());

    }

    If you want to learn more about database function you can look at 11-11 recipe in my book.



    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress http://weblogs.asp.net/zeeshanhirani
    Saturday, June 19, 2010 2:53 AM
  • thanks. I am also told this is another method

     

       var a = (from row in e.tests group row by new{dateKey=row.dt.Year+(row.dt.Month/12.0)+(row.dt.Day/31.0), actualDate=row.dt} into g select new { key = g.Key.dateKey, actualDate=g.Key.actualDate, count = g.Count() });


    If you do SharePoint dev and have MSN add me!! punkouter@hotmail.com We can help each other out. My SharePoint Blog ---> http://punkouter.spaces.live.com/
    Monday, June 21, 2010 3:34 PM
  • Just grouping by the day  works.. but then how do I get project other fields into my class like this... (want to get the NOTES value as well)

     

     

     

     

     var q = from app in dc.ApplicationLogs
              where app.Notes == "Created" || app.Notes == "Submitted"
              group app by new { SqlFunctions.DatePart("dy", app.LogDate.Value.Day), app.Notes } into g
              select new DailyAgg
              {
                Day = g.Key.Value,
                Count = g.Count(),
                Notes = g.Key.Notes
              };
    Error 3 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access. C:\Users\keaherb7\Desktop\CapRep7\CapRep7.Web\Service1.svc.cs 58 40 CapRep7.Web


    If you do SharePoint dev and have MSN add me!! punkouter@hotmail.com We can help each other out. My SharePoint Blog ---> http://punkouter.spaces.live.com/
    Tuesday, June 29, 2010 6:59 PM