LINQ to SQL: Select count rows for each date in a provided range?

Question

I have a table Account with a column called CreatedOn (nullable datetime).

I'm writing a method that takes two parameters from and to (DateTime objects) and I want to select all the dates between from and to along with the number of rows in T that where created on each of those dates.

How can I do this in an elegant and effective way in LINQ?

I tried to do it this way, but it seems to query the database once for each date in the list dates:

static void Test(DataClasses1DataContext context, DateTime from, DateTime to)
{
    List<DateTime> dates = new List<DateTime>();
    while (from <= to)
    {
        dates.Add(from.Date);
        from = from.AddDays(1);
    }

    var result = dates.Select(d => new { Date = d, Count = context.Accounts.Count(a => a.CreatedOn.HasValue && a.CreatedOn.Value.Date == d) });
    foreach (var row in result)
            Console.WriteLine(row.Date + "   " + row.Count);
}

It's not clear what you mean by the last part - if you want to group by other aspects, how would you expect that to work when you're only fetching "matches by day"? — Jon Skeet
– Jon Skeet, Commented Oct 24, 2011 at 14:33
Let's say that T also has another column called CreatedByAdmin. I would like the query to be easy to extend so that I can group by Date and CreatedByAdmin and select the number of rows for each such combination. — Nocklas
– Nocklas, Commented Oct 24, 2011 at 14:37
It's not really clear what you'd want the shape of the results of that query to be, to be honest - especially not starting with something already just grouped by date. — Jon Skeet
– Jon Skeet, Commented Oct 24, 2011 at 14:38
Ok, I have rephrased the question a little, so don't bother too much about extensibility. — Nocklas
– Nocklas, Commented Oct 24, 2011 at 15:05

Geoff Appleford · Accepted Answer · 2011-10-25 03:18:51Z

4

Updated in Response to comment

I wouldn't say its elegant but it does only query the database once.

static void Test(DataClasses1DataContext context, DateTime fromDate, DateTime toDate)
{
    var result = context.Accounts
                .Where(p => p.CreatedOn >= fromDate && p.CreatedOn <= toDate)
                .GroupBy(x => x.CreatedOn.Date)
                .Select(x => new {
                   dt = x.Key,
                   count = x.Count()});
}

List<DateTime> dates = new List<DateTime>();
while (fromDate <= toDate)
{
    dates.Add(fromDate.Date);
    fromDate = fromDate.AddDays(1);
}    

var allDates = dates.Select(x => new {
                    dt = x,
                    count = 0});

// Merge both lists and then filter to include highest count
var result = rows.Concat(allDates)
                 .GroupBy(x => x.dt)
                 .Select(x => new {
                    dt = x.Key,
                    count = x.OrderByDescending(c => c.count)
                             .FirstOrDefault().count});

edited Oct 25, 2011 at 3:18

answered Oct 24, 2011 at 15:52

Geoff Appleford

18.9k4 gold badges66 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nocklas Over a year ago

Your approach works if you only want the dates that have rows, but I also want the ones that have zero rows. I could of course create a new list with all the dates and use the value from result if there is one and zero otherwise. Is there a more elegant way of doing this?

Collectives™ on Stack Overflow

LINQ to SQL: Select count rows for each date in a provided range?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related