none
SQL to LINQ help needed RRS feed

  • Question

  • Hi

    I am very new to LINQ. I have below SQL. What is the equivalent LINQ please.

    Thanks

    Regads

    SELECT     TOP (5) tblStaff.ID, tblClients.Company, COUNT(tblStaffBookings.ID) AS Jobs
    FROM         tblStaff INNER JOIN
                          tblStaffBookings ON tblStaff.ID = tblStaffBookings.StaffID INNER JOIN
                          tblEvents ON tblStaffBookings.EventID = tblEvents.ID INNER JOIN
                          tblClients ON tblEvents.ClientID = tblClients.ID
    WHERE NOT EXISTS (SELECT StaffID FROM tblStaffNotPreferred WHERE tblStaffNotPreferred.staffid = tblStaff.id)
    GROUP BY tblStaff.ID, tblClients.Company, tblStaffBookings.ID
    ORDER BY Jobs DESC

    Tuesday, December 3, 2013 4:31 PM

Answers

  • Hello,

    The equivalent LINQ:

    var result = (from Staff in db.tblStaffs
                                  join StaffBooking in db.tblStaffBookings on Staff.ID equals StaffBooking.StaffID
                                  join Event in db.tblEvents on StaffBooking.EventID equals Event.ID
                                  join Client in db.tblClients on Event.ClientID equals Client.ID
                                  where !db.tblStaffNotPreferreds.Any(t => t.StaffID == Staff.ID)
                                  select new { StaffID = Staff.ID, Company = Client.Company, StaffBookingID = StaffBooking.ID } into tblnew
                                  group tblnew by new { tblnew.StaffID, tblnew.Company, tblnew.StaffBookingID } into tblgroupby
                                  select new { StaffID = tblgroupby.Key.StaffID, Company = tblgroupby.Key.Company, Jobs = tblgroupby.Count() }).Take(5);
    

    However, I am strange that since you want to count the StaffBookingID, why you need to group by it.

    As far as I know, if we want to count some fields, we usually do not need to group by them.

    If we do not want to group by the StaffBookingID, then we should change the linq query to below:

      var result = (from Staff in db.tblStaffs
    
                                  join StaffBooking in db.tblStaffBookings on Staff.ID equals StaffBooking.StaffID
    
                                  join Event in db.tblEvents on StaffBooking.EventID equals Event.ID
    
                                  join Client in db.tblClients on Event.ClientID equals Client.ID
    
                                  where !db.tblStaffNotPreferreds.Any(t => t.StaffID == Staff.ID)
    
                                  select new { StaffID = Staff.ID, Company = Client.Company, StaffBookingID = StaffBooking.ID } into tblnew
    
                                  group tblnew by new { tblnew.StaffID, tblnew.Company } into tblgroupby
    
                                  select new { StaffID = tblgroupby.Key.StaffID, Company = tblgroupby.Key.Company, Jobs = tblgroupby.Count() }).Take(5);
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Y a h y a Wednesday, December 4, 2013 5:47 AM
    Wednesday, December 4, 2013 2:25 AM
    Moderator

All replies

  • Hello,

    The equivalent LINQ:

    var result = (from Staff in db.tblStaffs
                                  join StaffBooking in db.tblStaffBookings on Staff.ID equals StaffBooking.StaffID
                                  join Event in db.tblEvents on StaffBooking.EventID equals Event.ID
                                  join Client in db.tblClients on Event.ClientID equals Client.ID
                                  where !db.tblStaffNotPreferreds.Any(t => t.StaffID == Staff.ID)
                                  select new { StaffID = Staff.ID, Company = Client.Company, StaffBookingID = StaffBooking.ID } into tblnew
                                  group tblnew by new { tblnew.StaffID, tblnew.Company, tblnew.StaffBookingID } into tblgroupby
                                  select new { StaffID = tblgroupby.Key.StaffID, Company = tblgroupby.Key.Company, Jobs = tblgroupby.Count() }).Take(5);
    

    However, I am strange that since you want to count the StaffBookingID, why you need to group by it.

    As far as I know, if we want to count some fields, we usually do not need to group by them.

    If we do not want to group by the StaffBookingID, then we should change the linq query to below:

      var result = (from Staff in db.tblStaffs
    
                                  join StaffBooking in db.tblStaffBookings on Staff.ID equals StaffBooking.StaffID
    
                                  join Event in db.tblEvents on StaffBooking.EventID equals Event.ID
    
                                  join Client in db.tblClients on Event.ClientID equals Client.ID
    
                                  where !db.tblStaffNotPreferreds.Any(t => t.StaffID == Staff.ID)
    
                                  select new { StaffID = Staff.ID, Company = Client.Company, StaffBookingID = StaffBooking.ID } into tblnew
    
                                  group tblnew by new { tblnew.StaffID, tblnew.Company } into tblgroupby
    
                                  select new { StaffID = tblgroupby.Key.StaffID, Company = tblgroupby.Key.Company, Jobs = tblgroupby.Count() }).Take(5);
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Y a h y a Wednesday, December 4, 2013 5:47 AM
    Wednesday, December 4, 2013 2:25 AM
    Moderator
  • Hi Fred

    Yes you are right there is no need to group on StaffBookingID. Many thanks for all your help. Much appreciated.

    Regards

    Wednesday, December 4, 2013 5:47 AM