locked
Count the number of retirees between two year with linq RRS feed

  • Question

  • User-347328420 posted

    Hello! I want to count the number of retirees, depending on the age of a person between two year. I would like to know how we can make this request with linq.
    I want to do something like this:

    var count_retirement = (from d in db.agent
                           where ((between (end_year - start_year)) - d.dateofbirth.Value.Year()) >= 60 //end_year and start_year is a dropdownlist
                           select d).Count();

    Thank you very much for your help!!

    Friday, November 11, 2016 1:07 AM

Answers

  • User-1509636757 posted

    I would suggest to add 60 years in DateOfBirth field and then check the resultant value comes between StartYear and EndYear or not. Something like,

    var count_retirement = (from d in db.agent
                           where d.dateofbirth.GetValueOrDefault().AddYears(60).Year >= start_year and d.dateofbirth.GetValueOrDefault().AddYears(60).Year <= end year
                           select d).Count();

    hope it helps./.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 11, 2016 11:05 AM

All replies

  • User303363814 posted

    Can you give some examples of values for your parameters that are supposed to be included in the count and some that are supposed to be excluded?

    Maybe create a table with some values for end_year, start_year, dateofbirth and a column indicating if the combination should be counted..

    Friday, November 11, 2016 4:54 AM
  • User-2057865890 posted

    Hi Christian,

    If you mean this.

    age>=60

    age between start_year and end_year

    You could refer this.

    class Program
        {
            static void Main(string[] args)
            {
                var list = new List
                {
                    new agent { dateofbirth=new DateTime(1950,1,1) },
                    new agent { dateofbirth=new DateTime(1960,1,1) },
                    new agent { dateofbirth=new DateTime(1949,1,1) },
                    new agent { dateofbirth=new DateTime(1952,1,1) },
                };
    
                var end_year = 1950;
                var start_year = 1940;
    
                var ret = from d in list
                          where (d.dateofbirth.Year >= start_year)
                          && (d.dateofbirth.Year <= end_year)
                          && (d.dateofbirth.Year + 60 <= DateTime.Today.Year)
                          select d;
    
                var count_retirement = ret != null ? ret.Count() : 0;
    
                foreach (var item in ret)
                {
                    Console.WriteLine(item.dateofbirth.Year);
                }
                Console.WriteLine(count_retirement);
            }
        }
    
        public class agent
        {
            public DateTime dateofbirth { get; set; }
        }

    Best Regards,

    Chris

    Friday, November 11, 2016 10:00 AM
  • User-1509636757 posted

    I would suggest to add 60 years in DateOfBirth field and then check the resultant value comes between StartYear and EndYear or not. Something like,

    var count_retirement = (from d in db.agent
                           where d.dateofbirth.GetValueOrDefault().AddYears(60).Year >= start_year and d.dateofbirth.GetValueOrDefault().AddYears(60).Year <= end year
                           select d).Count();

    hope it helps./.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 11, 2016 11:05 AM
  • User-347328420 posted

    Thank you very much Sir! Just Linq to Entities does not recognized the method AddYears, I am compelled to use EntityFunctions.

    Sunday, November 13, 2016 7:04 PM
  • User303363814 posted

    Are you trying to find the people who are over the age of 60 between a pair of years?

    If  you need to know the people who are over the age of 60 at the end of the last year (if they were over 60 at the start of the first year then they will still be over 60 at the end of the last year).

    If someone was over the age of 60 at the end of a given year then you can work out what the last possible date of birth was.

    For example, if the end_year is ey then the person needs to have been born before 1/1/(ey-59). 

    var lastBirth = new DateTime(end_year - 59, 1, 1);
    var retiredCount = db.agent.Where(a => a.DateOfBirth < lastBirth).Count();

    BUT ... don't trust a random piece of code that you find on the internet.  Test, test, test.

    (Just curious, your last post says that the suggestion from the previous post did not work.  Why mark it as an answer if the code does not even run?)

    Monday, November 14, 2016 4:24 AM