EFCore Getting Differences between two tables

Question

I am trying to get the differences between two tables, let's call them YestedayEmployees and TodaysEmployees. I am currently getting all both tables and then checking each item to see if the employee status changed or if they were deleted (they appear in yesterday's but not in today's table). This was fine when it was a smaller number of records but as the records increase, the network and compute overhead on my server are becoming a problem. Is there a way to do this as a linq query in EFCore? (or even two one for the deleted and one for the changed)

Answer 1

Please refer the following query statement:

Test data (you could get the table value from database using EF core, more detail information about using EF core with asp.net MVC, check this link):

        List<Employee> todayEmployees = new List<Employee>()
        {
            new Employee(){ EmpID=1001, EmpName="David", Status="OT" },
            new Employee(){ EmpID=1002, EmpName="Tom", Status="Off-line" },
            new Employee(){ EmpID=1003, EmpName="Jason", Status="OT" },
            new Employee(){ EmpID = 1004, EmpName="Dick", Status="Off-line" },
            new Employee(){ EmpID = 1005, EmpName="Cece", Status="OT" },
            new Employee(){ EmpID = 1006, EmpName="Dillion", Status="OT" },
            new Employee(){ EmpID = 1007, EmpName="Jeffery", Status="Off-Line" }
        };

        List<Employee> yesterdayEmployees = new List<Employee>()
        {
            new Employee(){ EmpID=1001, EmpName="David", Status="OT" },
            new Employee(){ EmpID=1002, EmpName="Tom", Status="OT" },
            new Employee(){ EmpID=1003, EmpName="Jason", Status="OT"},
            new Employee(){ EmpID = 1004, EmpName="Dick", Status="OT" },
            new Employee(){ EmpID = 1005, EmpName="Cece", Status="Off-Line" }
        };

To get the Employee which status is changed, we could use Join clause and where clause to compare the employee status:

        // get the employees which changes status
        var result = (from t in todayEmployees
                      join y in yesterdayEmployees
                      on t.EmpID equals y.EmpID
                      where (t.Status != y.Status)
                      select t).ToList();

Output:

        //get the employees status change information
        var query3 = (from t in todayEmployees
                      join y in yesterdayEmployees
                      on t.EmpID equals y.EmpID
                      where (t.Status != y.Status)
                      select new EmployeeViewModel()
                      {
                          EmpID = t.EmpID,
                          EmpName = t.EmpName,
                          StatusChangeLog = "change status from " + t.Status + " to " + y.Status
                      }).ToList();

Output:

To get the Employees which in the TodayEmployees Table, but not exist in the YesterdayEmployee Table, we could use the contains method to determines whether a sequence contains a specified element.

        //get the employees, which in TodayEmployees Table, but not exist in the YesterdayEmployee
        var query4 = (from t in todayEmployees 
                      where !(from y in yesterdayEmployees select y.EmpID).Contains(t.EmpID)
                      select t).ToList();

        var query5 = todayEmployees.Where(c => !yesterdayEmployees.Select(y => y.EmpID).Contains(c.EmpID)).Select(t => t).ToList();

Output: