CODEWITHSUNDEEP
c#linq-to-sql
Fran CD
Fran CD

Reputation: 327

Linq-to-SQL group by date-periods depending on a parameter (daily, Monthly....)

I'm trying to make a Linq to SQL query that returns Date grouping results. The challenge is about grouping daily/weekly/monthly/quarterly depends of a enumerable parameter (periodicity). Below, my current Linq to SQL query: 

var TotalGroupedInvoices = from c in entidades.InvoicesView
                     group c by (periodo == periodicity.Daily) ? c.InvoiceDate.Date :
                                      period == periodicity.Weekly?     c.InvoiceDate.Date.AddDays(-(double)c.InvoiceDate.DayOfWeek) :
                                      period == periodicity.Monthly? new DateTime(c.InvoiceDate.Year,c.InvoiceDate.Month ,1) :
                                      period == periodicity.Quarterly? new DateTime(c.InvoiceDate.Year, c.InvoiceDate.Month - (c.InvoiceDate.Month % 3) +1, 1) :
                                      period == periodicity.Anual ? new DateTime(c.InvoiceDate.Year, 1, 1) : inicio into Periods
                     select new
                     {

                         period = Periods.Key,
                         Total = Periodos.Sum(c => c.Total)
                     };

For clarification, take a look at the quarterly period code fragment:

period == periodicity.Quarterly? new DateTime(c.InvoiceDate.Year, c.InvoiceDate.Month - (c.InvoiceDate.Month % 3) +1, 1)

Thus, for dates into first quarter like: January/12/2012, January/15/2012, or March/20/2012 i get all of them grouped at the quarter begginig: January/1/2012, so it's works as expected.

First I wonder about the query efficiency. What do you think about this? Maybe it would be better to translate periods in integers for SQL Server efficiency and re-translate to date periods on client, but i'm not sure about this.

On the other hand, the weekly group works grouping dates weekly into the first sunday of each week:

period == periodicity.Weekly? c.InvoiceDate.Date.AddDays(-(double)c.InvoiceDate.DayOfWeek)

...but that's incorrect for me because i'm from Spain and weeks start on Monday. How can i fix the week groups to take this into account?

So, summarizing:

Thanks a lot! PD: sorry for my English level.

Upvotes: 1

Views: 1885

Answers (2)

Amy B
Amy B

Reputation: 110111

To answer the first part, you don't really want to transmit the enum value into the databaes and let it make the decision of which code branch to use. You have all the information to make the decision locally.

Expression<Func<InvoicesView, DateTime>> groupingExpr = c => c.InvoiceDate.Date;

if (period == periodicity.Weekly)
{
  groupingExpr = c => c.InvoiceDate.Date.AddDays(-(double)c.InvoiceDate.DayOfWeek);
}
else if (period == periodicity.Monthly)
{
  groupingExpr = c => new DateTime(c.InvoiceDate.Year,c.InvoiceDate.Month ,1);
}
else if (period == periodicity.Quarterly)
{
  groupingExpr = c => new DateTime(c.InvoiceDate.Year, c.InvoiceDate.Month - (c.InvoiceDate.Month % 3) +1, 1);
}
else if (period == periodicity.Anual
{
  groupingExpr = c => new DateTime(c.InvoiceDate.Year, 1, 1);
}


var TotalGroupedInvoices = entidades.InvoicesView 
  .GroupBy(groupingExpr)
  .Select(grouped => new {
    period = grouped.Key,
    Total = grouped.Sum(c => c.Total)   
  });

Here's the best I can do to blend the groupingExpr with query comprehension syntax:

var TotalGroupedInvoices = from grouped in entidades.InvoicesView.GroupBy(groupingExpr)
  select new {
    period = grouped.Key,
    Total = grouped.Sum(c => c.Total)   
  };

Upvotes: 2

kaj
kaj

Reputation: 5251

To answer the second part, you just need to do some "day" arithmetic. You can do this inline or as a separate function (eg extension method): 

public static DateTime FirstDayOfWeek (this DateTime date)  
{  
  double offset = (date.DayOfWeek == DayOfWeek.Sunday) ? -6 : (DayOfWeek.Monday - date.DayOfWeek);  
  return date.AddDays(offset);  
}

Then in your LINQ: 

period == periodicity.Weekly ? c.InvoiceDate.FirstDayOfWeek

However, given that you are retrieving this from SQL you may just want to look at the date functions there eg DATEPART and return them in your queries

Upvotes: 0

Related Questions