Linq to Entity Birthday Comparison

Question

I have a requirement to create a query using Linq to Entities where the birthday should be within 2 days ago and the next 30 days.

Nothing returns below:

DateTime twoDaysAgo = DateTime.Now.AddDays(-2); int twoDaysAgoDay = twoDaysAgo.Day; int twoDaysAgoMonth = twoDaysAgo.Month; DateTime MonthAway = DateTime.Now.AddDays(30); int monthAwayDay = MonthAway.Day; int monthAwayMonth = MonthAway.Month; var bdays = from p in db.Staffs where EntityFunctions.TruncateTime(p.BirthDate) > EntityFunctions.TruncateTime(twoDaysAgo) && EntityFunctions.TruncateTime(p.BirthDate) < EntityFunctions.TruncateTime(MonthAway) orderby p.BirthDate select p; return bdays; 

The problem I am facing is that I need something where, if the birthday falls from 11/3 to 12/5, he should return it. The reason she fails because birthdays include Year. However, when I use something like:

 p.BirthDate.Value.Month 

I get the error that this is not Linq support for Entities. Any help would be appreciated.

Answer 1

Independent of the year decision:

 void Main() { var birthdays = new List<DateTime>(); birthdays.Add(new DateTime(2013, 11, 08)); birthdays.Add(new DateTime(2012, 05, 05)); birthdays.Add(new DateTime(2014, 05, 05)); birthdays.Add(new DateTime(2005, 11, 08)); birthdays.Add(new DateTime(2004, 12, 31)); foreach(var date in birthdays.Where(x => x.IsWithinRange(twoDaysAgo, MonthAway))){ Console.WriteLine(date); } } public static class Extensions { public static bool IsWithinRange(this DateTime @this, DateTime lower, DateTime upper){ if(lower.DayOfYear > upper.DayOfYear){ return (@this.DayOfYear > lower.DayOfYear || @this.DayOfYear < upper.DayOfYear); } return (@this.DayOfYear > lower.DayOfYear && @this.DayOfYear < upper.DayOfYear); } } 

Exit from

 DateTime twoDaysAgo = DateTime.Now.AddDays(-2); DateTime MonthAway = DateTime.Now.AddDays(30); 8/11/2013 0:00:00 8/11/2005 0:00:00 

Exit from

 DateTime twoDaysAgo = new DateTime(2012, 12, 25); DateTime MonthAway = new DateTime(2013, 01, 05); 31/12/2004 0:00:00 

Answer 2

If you want to ignore the year value, how about using the DayOfYear function?

 var bdays = from p in db.Staffs where EntityFunctions.DayOfYear(p.BirthDate) > EntityFunctions.DayOfYear(twoDaysAgo) && EntityFunctions.DayOfYear(p.BirthDate) < EntityFunctions.DayOfYear(MonthAway) orderby p.BirthDate select p; 

Answer 3

You can change all the years to date from the year does not matter, and then you can check it this way.

  DateTime twoDaysAgo = DateTime.Today.AddDays(-2); DateTime monthAway = DateTime.Today.AddMonths(1); List<DateTime> checkDates = new List<DateTime> { new DateTime(2011, 11, 3), new DateTime(2011, 12, 5), new DateTime(2011, 12, 6), new DateTime(2011, 11, 2) }; checkDates = checkDates.Select(x => new DateTime(DateTime.Today.Year, x.Month, x.Day)).ToList(); var bdays = from p in checkDates where (p >= twoDaysAgo && p <= monthAway) || (p>= twoDaysAgo.AddYears(-1) && p <= monthAway.AddYears(-1)) orderby p select p; 

The result is

 11/3/2013 12:00:00 AM 12/5/2013 12:00:00 AM 

This also works with the following list of dates when today is a new DateTime(2013, 12, 31)

 List<DateTime> checkDates = new List<DateTime> { new DateTime(2011, 12, 29), new DateTime(2011, 12, 28), new DateTime(2011, 1, 30), new DateTime(2011, 2, 2) }; 

Giving Results

 1/30/2013 12:00:00 AM 12/29/2013 12:00:00 AM 

Answer 4

How about adding nr. years from date of birth to today?
Something like: (Unverified)

 var now = DateTime.Now; var twoDaysAgo = now.AddDays(-2); var monthAway = now.Now.AddDays(30) var bdays = from p in db.Staffs let bDay = EntityFunctions.AddYears(p.BirthDate, EntityFunctions.DiffYears(now, p.BirthDate)) where bDay > twoDaysAgo && bDay < monthAway orderby p.BirthDate select p;