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.

Possible duplicate of [Entity Framework: Efficiently grouping by month](https://stackoverflow.com/questions/9886190/entity-framework-efficiently-grouping-by-month) — mjwills, Jan 07 '19 at 00:25
if currently understood you need to get all the results as objects instead of counting how many results matching your statement??? Then why don't you remove the .Count() at the end? — M.Hazara, Jan 07 '19 at 00:28

score 1 · Accepted Answer · answered Jan 07 '19 at 00:22

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();
}

score -1 · Answer 2 · answered Jan 07 '19 at 00:37

This is your original Linq statement:

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();

you are asking:

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.

you can change it to:

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);
var totalCount = TotalSmsJan.Count();
var totalForPeriod = TotalSmsJan.Where(s => s.MessageDate >= 'your lower date' && s.MessageDate <= 'your upper date').Count();

Get count of date occurrence between 2 dates using LINQ

2 Answers2