locked
LINQ to retrieve the number of users registered per month RRS feed

  • Question

  • User-1266083892 posted

    Hi,

    I have a user table, which contains the users registered to an application.

    I need to retrieve the list  of number of users registered each month . The month should be taken from the CreatedOn field in the same table.

    I need to generate report based on this data. so i need the output as follows

    UserCount    Month

    2                    Jan

    3                   Feb

    I am using mvc5 with EF6 to retrieve  the data. How can i retrieve the data in the above format using linq/lambda expression.

    Wednesday, February 21, 2018 11:57 AM

Answers

  • User1400794712 posted

    Hi priya.renjith,

    We can use groupby to group users by the Moth of the create date. Then use count() method to figure out the number of users for each month. I make a demo, you can refer to it:

    Model:

    public class User
    {
        public int ID { get; set; }
        public string UserName { get; set; }
        public DateTime CreatedOn { get; set; }
    }

    Controller:

    public class UserData
    {
        public int UserCount { get; set; }
        public string Month { get; set; }
    }
    private Test1Context db = new Test1Context();
    public ActionResult Index()
    {
        var list = db.User.GroupBy(u => u.CreatedOn.Month)
            .Select(u => new UserData
            {
                UserCount = u.Count(),
                Month = u.FirstOrDefault().CreatedOn.Month.ToString()
            }).ToList();
        foreach (var userdata in list)
        {
            switch (userdata.Month)
            {
                case "1":
                    userdata.Month = "Jan";
                    break;
                case "2":
                    userdata.Month = "Feb";
                    break;
                case "3":
                    userdata.Month = "Mar";
                    break;
                case "4":
                    userdata.Month = "Apr";
                    break;
                case "5":
                    userdata.Month = "May";
                    break;
                case "6":
                    userdata.Month = "Jun";
                    break;
                case "7":
                    userdata.Month = "Jul";
                    break;
                case "8":
                    userdata.Month = "Aug";
                    break;
                case "9":
                    userdata.Month = "Sep";
                    break;
                case "10":
                    userdata.Month = "Oct";
                    break;
                case "11":
                    userdata.Month = "Nov";
                    break;
                case "12":
                    userdata.Month = "Dec";
                    break;
                default:
                    userdata.Month = "error";
                    break;
            }
        }
        return View();
    }

    How it works:

    Best Regards,

    Daisy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 22, 2018 3:06 AM
  • User-1266083892 posted

    Hi,

    This way I am getting the list of month with the counts. But ,i need the list of last three months.

    If  there is no user registered, the count should show 0.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 23, 2018 6:04 AM

All replies

  • User1400794712 posted

    Hi priya.renjith,

    We can use groupby to group users by the Moth of the create date. Then use count() method to figure out the number of users for each month. I make a demo, you can refer to it:

    Model:

    public class User
    {
        public int ID { get; set; }
        public string UserName { get; set; }
        public DateTime CreatedOn { get; set; }
    }

    Controller:

    public class UserData
    {
        public int UserCount { get; set; }
        public string Month { get; set; }
    }
    private Test1Context db = new Test1Context();
    public ActionResult Index()
    {
        var list = db.User.GroupBy(u => u.CreatedOn.Month)
            .Select(u => new UserData
            {
                UserCount = u.Count(),
                Month = u.FirstOrDefault().CreatedOn.Month.ToString()
            }).ToList();
        foreach (var userdata in list)
        {
            switch (userdata.Month)
            {
                case "1":
                    userdata.Month = "Jan";
                    break;
                case "2":
                    userdata.Month = "Feb";
                    break;
                case "3":
                    userdata.Month = "Mar";
                    break;
                case "4":
                    userdata.Month = "Apr";
                    break;
                case "5":
                    userdata.Month = "May";
                    break;
                case "6":
                    userdata.Month = "Jun";
                    break;
                case "7":
                    userdata.Month = "Jul";
                    break;
                case "8":
                    userdata.Month = "Aug";
                    break;
                case "9":
                    userdata.Month = "Sep";
                    break;
                case "10":
                    userdata.Month = "Oct";
                    break;
                case "11":
                    userdata.Month = "Nov";
                    break;
                case "12":
                    userdata.Month = "Dec";
                    break;
                default:
                    userdata.Month = "error";
                    break;
            }
        }
        return View();
    }

    How it works:

    Best Regards,

    Daisy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 22, 2018 3:06 AM
  • User-1266083892 posted

    Hi Daisy,

    Thanks for your response. But it is not accepting Month. Showing the following error

    DateTime? doesnot contain a definifition for Month and no extension method Month accepting a first argument of type 'DateTime?'

    Should use like this   u.CreatedOn.Value.Month

    Thanks a lot, its working fine.

    Thursday, February 22, 2018 4:39 AM
  • User-1266083892 posted

    Hi,

    This way I am getting the list of month with the counts. But ,i need the list of last three months.

    If  there is no user registered, the count should show 0.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 23, 2018 6:04 AM
  • User1400794712 posted

    Hi divya.iyer ,

    Yes, you're right. Since I'm using Datetime type for month, we can use u.CreatedOn.Month. If using Datetime?, we need to use u.CreatedOn.Value.Month.

    In my code, the month value comes form the table's CreatedOn. For example, if there is no record in April, we won't get such month in records.

    If you want to get the last three months, no matter it exist in the records, the code can be like this:

    //For exmaple, the last three is 2,3,4
    //Set them in an array.
    int[] months = { 2, 3, 4 }; List<UserData> userdatas = new List<UserData>(); foreach(var month in months) { userdatas.Add(new UserData { Month = month.ToString(), UserCount = db.User.Where(u => u.CreatedOn.Month == month).Count()
    //If you are using Datetime?, the code should be:
    //UserCount = db.User.Where(u => u.CreatedOn.Value.Month == month).Count() }); } foreach (var userdata in userdatas) { switch (userdata.Month) { case "1": userdata.Month = "Jan"; break; case "2": userdata.Month = "Feb"; break; case "3": userdata.Month = "Mar"; break; case "4": userdata.Month = "Apr"; break; case "5": userdata.Month = "May"; break; case "6": userdata.Month = "Jun"; break; case "7": userdata.Month = "Jul"; break; case "8": userdata.Month = "Aug"; break; case "9": userdata.Month = "Sep"; break; case "10": userdata.Month = "Oct"; break; case "11": userdata.Month = "Nov"; break; case "12": userdata.Month = "Dec"; break; default: userdata.Month = "error"; break; } }

    How it works:

    Best Regards,

    Daisy

    Friday, February 23, 2018 9:24 AM
  • User-1266083892 posted

    Hi Daisy,

    Thanks a lot for the help. Now I am able to display the data in the following format.

    Name   Jan   Feb  Mar

    ABC      2        1     1

    XYZ     3      1

    But , now I want to show one Total row as the last row in the table. Which should show the total count  for each month

    Is it possible to include this in linq? Now I looping through the  list and dynamically generating the table. How can i add this total row in the last?

    In this case ,the last row will be like

    Total   5     2     1

    Wednesday, February 28, 2018 6:56 AM