locked
Group By Using LINQ Query in MVC RRS feed

  • Question

  • User271039688 posted

    Hello,

    I have some data that represents the number of hours leave requested by a employee. What I want to do is to group this data so that the number of hours requested leave is shown on a monthly basis for each employee and then this information is presented in a Microsoft Report. 

    I have tried using the following LINQ query to achieve this but I just get an error each time. Could someone please tell me what I am doing wrong here?

           var refine = from i in context.leave_requests
                                         where i.staff_member_id == model.selectedStaffId && i.staff_member_allowance.staff_member_department == model.selectedDepartmentId
                                         group i by Convert.ToDateTime(i.leave_request_from_date).Month into grp
                                         select new { Month = grp.Key, Count = grp.Sum(i => i.leave_request_days) };

    Wednesday, December 23, 2015 12:08 PM

Answers

  • User1577371250 posted

    Hi,

    Check this

    var refine = from i in context.leave_requests
                  let m = i.leave_request_from_date.Month
                where i.staff_member_id == model.selectedStaffId && i.staff_member_allowance.staff_member_department == model.selectedDepartmentId
                             group i by m into grp
                             select new { Month = grp.Key, Count = grp.Sum(i => i.leave_request_days) };

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 24, 2015 5:31 AM

All replies

  • User1577371250 posted

    Hi,

    try this

     var refine = from i in context.leave_requests
                  let m = Convert.ToDateTime(i.leave_request_from_date).Month
                where i.staff_member_id == model.selectedStaffId && i.staff_member_allowance.staff_member_department == model.selectedDepartmentId
                             group i by m into grp
                             select new { Month = grp.Key, Count = grp.Sum(i => i.leave_request_days) };

    Wednesday, December 23, 2015 2:14 PM
  • User271039688 posted

    Thank you!

    Wednesday, December 23, 2015 2:32 PM
  • User271039688 posted

    After further testing the error still remains :-( 

    LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.Object)' method, and this method cannot be translated into a store expression.

    Wednesday, December 23, 2015 2:47 PM
  • User2053451246 posted

    Convert the date first:

    var FromDate = Convert.ToDateTime(i.leave_request_from_date).Month;

    let m = FromDate
    Wednesday, December 23, 2015 3:25 PM
  • User271039688 posted

    If you do what you suggested though, it doesn't recongise i?

    Wednesday, December 23, 2015 3:32 PM
  • User271039688 posted
    Will this work?


    var request = context.leave_requests.Where(m => m.staff_member_id == model.selectedStaffId && m.staff_member_allowance.staff_member_department == model.selectedDepartmentId).Select(m => m.leave_request_date).FirstOrDefault(); var FromDate = Convert.ToDateTime(request).Month; var refine = from i in context.leave_requests let m = FromDate where i.staff_member_id == model.selectedStaffId && i.staff_member_allowance.staff_member_department == model.selectedDepartmentId group i by m into grp select new { Month = grp.Key, Count = grp.Sum(i => i.leave_request_days) };

    Wednesday, December 23, 2015 3:38 PM
  • User2053451246 posted

    I think it should.  I wasn't aware that the date was coming from another field in the existing LINQ statement.  I think what you have should do the trick.

    Wednesday, December 23, 2015 5:21 PM
  • User1577371250 posted

    Hi,

    Check this

    var refine = from i in context.leave_requests
                  let m = i.leave_request_from_date.Month
                where i.staff_member_id == model.selectedStaffId && i.staff_member_allowance.staff_member_department == model.selectedDepartmentId
                             group i by m into grp
                             select new { Month = grp.Key, Count = grp.Sum(i => i.leave_request_days) };

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 24, 2015 5:31 AM