andrelange91 andrelange91 - 2 months ago 5
C# Question

Linq Current month values from DB

I need to get data from the current month.Haven't been able to find a solution that works.

This is my code, which gives me the data I need, but I get data from a whole month back, instead of from the current month we are in.

I chose the date "limit" twice with

row > DateTime.Today.Addmonths(-1)


Any ideas ?

var userQuery =
from timeEntry in TimeEntries
//this displays a month back, not current month TODO: change to current month.
where timeEntry.DateEntity > DateTime.Today.AddMonths(-1)
select new {
UserName = timeEntry.User.FirstName + " " +timeEntry.User.LastName,
HoursEntered = timeEntry.HoursEntered,
User = timeEntry.User
};

var localrows = userQuery.ToList();

var grouping = localrows.GroupBy(x => x.User);

var userList = grouping.Select(q => new {
UserName = q.FirstOrDefault().UserName,
Hours = q.Sum(hr => hr.HoursEntered), //AbsenceTypeID = holiday(1) // still takes from a whole month, instead of current month.
Holiday = q.FirstOrDefault().User.Absences.Where(a => a.AbsenceTypeID == 1).Where(date => date.DateEntity > DateTime.Today.AddMonths(-1)).Count(),
});


EDIT:

Keyur patel' answer worked, though not for the bottom one.

so there i have to thank Jules for this one:

DateTime.Today.AddDays((DateTime.Today.Day -1) * -1)

Answer

Try using this:

DateTime today = DateTime.Today;
var userQuery = 
from timeEntry in TimeEntries
where timeEntry.DateEntity.Month == today.Month && timeEntry.DateEntity.Year == today.Year
select new {
    UserName = timeEntry.User.FirstName + " " +timeEntry.User.LastName,
    HoursEntered = timeEntry.HoursEntered,       
    User = timeEntry.User
};

Added check for .Year since it may match with months from other years, if you have any.

Edit

As for the holiday part:

var userList = grouping.Select(q => new {
    UserName = q.FirstOrDefault().UserName,
    Hours = q.Sum(hr => hr.HoursEntered),
    Holiday = q.FirstOrDefault().User.Absences.Where(a => a.AbsenceTypeID == 1 && a.DateEntity.Month == today.Month && a.DateEntity.Year == today.Year).Count(),
});