linq join with where condition on both table columns and groupby

Question

I have two lists as above

Employee table shld be joined with class table(on empId) and min(finalenddate) shld be fetched....

Additionally the below conditions to be applied

1. Fetch only FinalEndDate s which are less than the NextDate(EmployeeTable)
2. Class table shld be grouped by empId,Class and min of Step1 shld be fetched....

So the expected output is

Note: the FinalEndDate of EmpId=1 is 30/9/2020 and not 31/1/2020

 List<EmployeeFinal> minFinal = (
                 from m in EmployeeTable
                 from ec in ClassTable.Where(x => m.EmpId == x.EmpId && m.NextDate < x.FinalEndDate).DefaultIfEmpty()
                 where ec != null
                 group ec by (new { ec.EmpId, ec.Class }) into h
                 select new EmployeeFinal
                 {
                     EmpId = h.FirstOrDefault().EmpId,
                     Class = h.FirstOrDefault().Class,
                     FinalEndDate = h.Min(m => m.FinalEndDate),
                 }).ToList();

This linq query yields me the desired result. But the employee table has 30000+ records and this executes for very long time(5-8 mins).

Is this output achievable using linq which has better performance.

Thanks, PRI

Answer 1

There is some improvement in your query, plz try this.

List<EmployeeFinal> minFinal = (
    from m in EmployeeTable
    join ec in ClassTable
        on m.EmpId equals ec.EmpId
        where m.NextDate < ec.FinalEndDate
    group ec by new { ec.EmpId, ec.Class } into h
    select new EmployeeFinal
    {
        EmpId = h.Key.EmpId,
        Class = h.Key.Class,
        FinalEndDate = h.Min(m => m.FinalEndDate)
    }).ToList();