C# Linq question: How to identify and count items in a set

Question

I have a friend who is using c# Linq to report on the following table:

varchar(50) name
datetime    when

The customer wants to pick what columns to summarize, like Total for this year, Total for Last year, Total for two weeks ago, etc. Each column would have some begin and end time they summarize with a count. I did a quick select statement for him:

select A.name as [Name],

    (select count(*) from mytable B where A.name = B.name and 
     datepart(yy, when) = datepart(yy,getdate())) as [This Year],

    (select count(*) from mytable B where A.name = B.name and
     datepart(yy, when) = (datepart(yy,getdate()) - 1)) as [Last Year]

from (select distinct name from mytable) A

I also suggested he make each calculation field a delegate so he could contain the calculation mess outside of the query. Assuming each delegate would have a start and end date which calculated how many hits occurs for each distinct Name, anyone want to take a stab at what the Linq c# code would look like?

Answer 1

Pretty simple translation:

DateTime Now = DateTime.Now;

DateTime thisYearStart = new DateTime(Now.Year, 1, 1);
DateTime thisYearEnd = thisYearStart.AddYears(1);
DateTime lastYearStart = thisYearStart.AddYears(-1);
DateTime lastYearEnd = thisYearStart

var query = dc.MyTable
  .GroupBy(a => a.Name)
  .Select(g => new
  {
    Name = g.Key,
    ThisYearCount = g
      .Count(b => thisYearStart <= b.When && b.When < thisYearEnd)
    LastYearCount = g
      .Count(b => lastYearStart <= b.When && b.When < lastYearEnd)
  });

---

Incidently, this is the refactored sql that I was translating from:

SELECT A.Name as [Name],
  SUM(CASE WHEN @start1 <= A.When AND A.When < @end1 THEN 1 ELSE 0 END) as ThisYear,
  SUM(CASE WHEN @start2 <= A.When AND A.When < @end2 THEN 1 ELSE 0 END) as LastYear
FROM MyTable A
GROUP BY A.Name

Answer 2

Here is a generic method that could be used with any table and date field and returns groups that you can count. You would have to run it multiple times for each date range you wanted data for.

static IQueryable<IGrouping<TKey, TSource>> GroupWithinDates<TSource, TKey>(
    this IQueryable<TSource> source,
    Expression<Func<TSource, TKey>> keySelector,
    Expression<Func<TSource, DateTime>> dateSelector,
    DateTime startDate,
    DateTime endDate
)
{
    return source
        .Where(item => dateSelector(item) >= startDate
                       && dateSelector(item) < endDate)
        .GroupBy(keySelector)
}

// Usage after setting startDate and endDate
var groups = mytable.GroupWithinDates(
               A => A.name, A => A.when, startDate, endDate)
// You can then transform to Name and Count
var groupCounts = groups.Select(g => new {Name = g.Key, Count = g.Count()};