Linq select where date is yesterday

Question

I've got as far as this:

DateTime yesterday = DateTime.Today.AddDays(-1);
YesterdaysRegistrations = db.tblForumAuthors.Where(c => c.Join_date == yesterday).Count();

There definitely records that have the join date of yesterday, but this returns 0 all the time! Can anyone tell me if I'm doing this correctly?

Answer 1

AddDays keeps the Hour/Minute/Second components. You either have to use (if c.Join_date is only the date component):

DateTime yesterday = DateTime.Today.AddDays(-1).Date;

Otherwise, you compare for range:

DateTime yesterday = DateTime.Today.AddDays(-1).Date;
DateTime yesterdayEnd = DateTime.Today.Date.AddSeconds(-1);
db.tblForumAuthors.Where(c => c.Join_date >= yesterday && c.Join_date < yesterdayEnd)

Answer 2

You don't have to strip off the time, you just have to make sure you don't do an exact match.

Try this instead:

DateTime today = DateTime.Today; // read once, avoid "odd" errors once in a blue moon
DateTime yesterday = today.AddDays(-1);
YesterdaysRegistrations = db.tblForumAuthors.Where(
    c => c.Join_date >= yesterday
      && c.Join_date < today).Count();