CODEWITHSUNDEEP
sqlasp.net-mvclinqgroup-bysql-order-by
user9630128
user9630128

Reputation:

LINQ using group by and ascending order

I have two tables attendancelogs and employees with following structure;

Attendance Logs Table:

Attendancelogs

Att_Id
Registeration_Id
DateTime
CheckType

Employees Table:

Employees
Id
Emp_Id
Enroll_Id

Now what I'm trying is to write a query that will fetch all the records from the attendancelogs that have their CheckType value as "Pending" and the Registeration_Id being equal to the Enroll_Id in the Employees table OrderByAcsending on the DateTime. In other words, get all the records from the attendancelogs table whos RegistrationId is equal to the Enroll_Id in the Employees table and their CheckTypeis Pending and they should be orderbyascending according to theDateTime`

What I tried was an ugly way of using foreach

//some method that populates a list
 ICollection<AttendanceLog> lstMachineInfo = manipulator.GetLogData(objZkeeper2, machineNum);
 List<Employee> empList = db.Employees.ToList();
  foreach (var emp in empList)
                        {
 var empLogs = lstMachineInfo.Where(x => x.RegisterationId == int.Parse(emp.EnrollNumber)).ToList();
 var prevDate = (from obj in empLogs select obj.Date).FirstOrDefault();
                            var prevDateTime = (from obj in empLogs select obj.DateTime).FirstOrDefault();
//and so on

This obviously is very costly when it comes to performance and I have minimal knowledge of optimizing the queries.

Upvotes: 0

Views: 94

Answers (2)

Florian
Florian

Reputation: 1481

Is this what you're looking for ?

//You are building your query to get all att ordered by date
var query = (from emp in db.Employees
             join att in db.Attendancelogs 
                 on emp.EnrollNumber equals att.RegisterationId 
             where att.CheckType.ToLower() == "pending"
             select att)
             .OrderBy(att => att.DateTime);

//You fire the query
List<Attendancelogs> attList = query.ToList();

Upvotes: 0

Ravi Solanki
Ravi Solanki

Reputation: 17

you can try following code on list of Attendance and Employee

var result = from attendence in AttendenceList
                   orderby attendence.DateTime
                   join employee in EmployeeList on attendence.Registeration_Id equals employee.Enroll_Id into employeejoin
                   from employ in employeejoin
                   orderby attendence.DateTime ascending
                   where attendence.Registeration_Id == employ.Enroll_Id && attendence.CheckType == "Pending"
                   orderby attendence.DateTime ascending
                   select attendence;

Upvotes: 1

Related Questions