Fetch data by upcoming birthdays in linq to sql

Question

I want to show list of people whose birthdays are in coming 15 days. I have below dates in my table column:

LET ME MORE CLEAR MY QUESTION

BELOW IS MY EMPLOYEE TABLE COLUMNS

EMP_ID  |EMP_TYPE   |EMP_USERNAME       |EMP_DOB
======= |========== |===============    |==================
1       |ADMIN      |ELENA GILBERT      |1993-02-19
2       |EMPLOYEE   |KATHERINE PIERCE   |1993-03-19
3       |EMPLOYEE   |STEFAN SALVATORE   |1993-04-19
4       |EMPLOYEE   |DAMON SALVATORE    |1993-05-19
5       |EMPLOYEE   |JEREMY GILBERT     |1993-05-20

Now I just want to show upcoming birthdays in 15 days. Below I created a custom class in which I set two properties:

public class Birthday
{
   public string Name { get; set; }
   public DateTime date { get; set; }
}

Below is my web method which return me a list from which I want just Emp_Username and Emp_DOB which upcoming within 15 days.

[WebMethod]
public static List<Birthday> getBirthday()
{
   var slist = new List<Birthday>();
   var db = new BLUEPUMPKINEntities();
   var query = (from emp in db.Employees
   let BirthdayDiff = (new DateTime(DateTime.Now.Year,
                      emp.EMP_DOB.Value.Month, emp.EMP_DOB.Value.Day) -                                                                                                         DateTime.Now).TotalDays where BirthdayDiff >= 0 && BirthdayDiff <= 15
                      select new Birthday { Name = emp.EMP_USERNAME, date = Convert.ToDateTime(emp.EMP_DOB) });
                      return slist.ToList();
    }

Problem is my above code is not working and not showing any errors in de-bugging.

Answer 1

You should change your query to something like this and then return it:

[WebMethod]
public static List<Employee> getBirthday()
{
    var db = new BLUEPUMPKINEntities();
    const int dateOffset = 15;

    var today = DateTime.Today;
    var maxDate = DateTime.Today.AddDays(dateOffset);

    return (from emp in db.Employees
            where emp.EMP_DOB.Value >= today
            where emp.EMP_DOB.Value <= maxDate
            select emp).ToList();
}

Answer 2

There are at least three issues in your code.

---

Firstly, this line can potentially produce incorrect results:

let BirthdayDiff = (new DateTime(DateTime.Now.Year, emp.EMP_DOB.Value.Month, emp.EMP_DOB.Value.Day) - DateTime.Now).TotalDays

Note that you use the current time to produce year

DateTime.Now.Year

And consider the following case:

Now is 25-Dec-15 and One of your Employee is having birthday in 3-Jan-16. According to the calculation, you would produce DateTime with value of 3-Jan-15 for your Employee and you minus it with DateTime.Now and thus you will get value < -300 in total days.

---

Secondly, don't use DateTime.Now more than once in a single query, because the result of the subsequent DateTime.Now may be different from the first one. Use only once:

DateTime now = DateTime.Now; //and then just use now

Or even better, to remove all hours and minutes discrepancy:

DateTime today = DateTime.Today;

---

And lastly, you never return the result of the query, but only an empty List.

Note that you define:

var slist = new List<Employee>();

And the query:

var db = new BLUEPUMPKINEntities();
var query = from emp in db.Employees
            let BirthdayDiff = (new DateTime(DateTime.Now.Year, emp.EMP_DOB.Value.Month, emp.EMP_DOB.Value.Day) - DateTime.Now).TotalDays
            where BirthdayDiff >= 0 && BirthdayDiff <= 15
            select emp;

But you neither relate your slist with query nor return the query itself. Thus, you always get nothing, because slist is always an empty, new List.

---

Minor Edit: change from db to db.Employees and adding ToList()

Correct three of them and you have a safe way to do get what you want (Note: beware of leap year):

[WebMethod]
public static List<Employee> getBirthday()
{
    var slist = new List<Employee>();
    var db = new BLUEPUMPKINEntities();
    var today = DateTime.Today; //2.
    return (from emp in db.Employees
            let BirthdayDiff = (new DateTime(today.Year, emp.EMP_DOB.Value.Month, emp.EMP_DOB.Value.Day) - today).TotalDays
            let TrueBirthdayDiff = BirthdayDiff >= 0 ? BirthdayDiff : BirthdayDiff + 365 + Convert.ToInt32(DateTime.IsLeapYear(now.Year)) //1, 3 and leap year
            where TrueBirthdayDiff >= 0 && TrueBirthdayDiff <= 15
            select emp).ToList();
}

Answer 3

By using below query I found a way to know upcoming birthdays. I don't know why folks down voted my this post.

var day = DateTime.Now.Day;
var month = DateTime.Now.Month;
            var query = (from emp in db.Employees
                     where emp.EMP_DOB.HasValue == true
                     && emp.EMP_DOB.Value.Day >= day && emp.EMP_DOB.Value.Month == month
                     select new Birthday
                     {
                         Name = emp.EMP_USERNAME,
                         date = emp.EMP_DOB.Value
                     }).ToList();

        return query;

Answer 4

You're returning an empty list...

[WebMethod]
public static IList<Employee> getBirthday()  //<-- changed signature to IList
{
    var slist = new List<Employee>();
    var db = new BLUEPUMPKINEntities();
    var query = from emp in db.Employees
                let BirthdayDiff = (new DateTime(DateTime.Now.Year,     
                emp.EMP_DOB.Value.Month, emp.EMP_DOB.Value.Day) - DateTime.Now).TotalDays
                where BirthdayDiff >= 0 && BirthdayDiff <= 15
                select emp;
    //return slist;  //<-- problem right here!
    return query.ToList();  //<-- this should fix it...!
}

If your heart and soul is really tied to that List<T>, then do this right before the return slist:

slist.AddRange(query);

HTH!