Entity Framwork, group by day/week/month working on a datetime

Question

As the title says, I have a datetime column in my table.

I need to group by my records in three ways, in three different cases:

1. group by day
2. group by week
3. group by month

How can I do these 3 different group by statement in Entity Framework, with lambda expression, if possible?

UPDATE: the linked question as duplicate, is not a real duplicate. I'm asking for the lambda solution and it isn't present in the link

Answer 1

you can use this method :

var ByDayGrouped = new TestEntities().Objects.GroupBy(O => new { O.date.Year, O.date.Month, O.date.Day }).ToList();

var ByMonthGrouped = new TestEntities().Objects.GroupBy(O => new { O.date.Year, O.date.Month }).ToList();

var ByWeekGrouped = new TestEntities().Objects.GroupBy(O => (O.date.DayOfYear % 7) + M ).ToList();

Answer 2

Use this

List<MyEntitiy> list = (from e in Context.MyEntities group e by e.Date.Day into g select g).ToList();

For Month replace e.Date.Day with with e.Date.Month

For Week you must first Calculate WeekOfYear

public int GetWeekOfYear(this DateTime d)
{
    GregorianCalendar c = new  GregorianCalendar();
    return c.GetWeekOfYear(d,CalenderWeekRule.FirstDay,DayOfWeek.Sunday);
}

And use

List<MyEntitiy> list = (from e in Context.MyEntities group e by e.Date.GetWeekOfYear() into g select g).ToList();

Answer 3

You can use the following format using linq and lambda expressions

For Group By Day

    var results = from p in db.table
                          group p.data by p.DateTime.UtcNow.Day into g
                          select new { Id = g.Key, parameters = g };

For Group By Week

    var result = from p in db.table
                         group p.data by p.DateTime.UtcNow.DayOfWeek into g
                          select new { Id = g.Key, parameters = g}

or else

    //get the 7days of the week by the below method and perform between operation
    DateTime fromDate = DateTime.Today.Subtract(new TimeSpan(7, 0, 0, 0));
    DateTime today = DateTime.Today;
    var result = from p in db.table
                         group p.data by (DbFunctions.TruncateTime(today) && DbFunctions.TruncateTime(today) >= DbFunctions.TruncateTime(fromDate)) into g
                         select new { Id = g.Key, parameters = g}

similarly for Month

    DateTime fromDate = DateTime.Today.Subtract(new TimeSpan(30, 0, 0, 0));
    DateTime today = DateTime.Today;
    var result = from p in db.table
                         group p.data by (DbFunctions.TruncateTime(today) && DbFunctions.TruncateTime(today) >= DbFunctions.TruncateTime(fromDate)) into g
                         select new { Id = g.Key, parameters = g}