Get count of date occurrence between 2 dates using LINQ

Question

I have a table called messages. I have a messagedate field. I want to count how many times messagedate occurs between 2 dates.

I am having trouble formulating the query in linq.

string currYear = DateTime.Now.Year.ToString();
var TotalSmsJan = (from x in db.Messages
                                     where (x.MessageDate  >= '1/1/@currYear') && (x.MessageDate >= '1 /31/@currYear')   
                                     select x).Count();

Ideally I would like the message count for each month, but would be happy to get them one at a time as in code above.

Answer 1

Treating strings as dates, like in your code, is not optimal.

The below code uses dates rather than strings, and uses the same basic structure as your current code. It would require 12 queries though (one per month).

var currYear = DateTime.Now.Year;

for (int month = 1; month <= 12; month++)
{
    var firstOfThisMonth = new DateTime(currYear, month, 1);
    var firstOfNextMonth = firstOfThisMonth.AddMonths(1);

    var totalSMS = (from x in db.Messages
                    where x.MessageDate >= firstOfThisMonth && x.MessageDate < firstOfNextMonth
                    select x).Count();
}