dhoni7 Posts - Answer Overflow

dhoni7

•Created by dhoni7 on 11/23/2023 in #help

EF/Linq Left joining two tables after filtering the first table

I need to perform left join Table1 on Table2, before I do that I have to filter table1 records with employee id, I right now have

List<Department> Departments, //this holds all my departments

List<Department> Departments, //this holds all my departments

I tried this but It says that Ienumeable does not have defintion for IQueryable, can someone please help how to perform this join operation. Thanks

var result = (from department in Departments
              from employeeId in department.Employees.Select(e => e.EmployeeId)
              join at in dbContext.Table1 on employeeId equals at.EmployeeId
              join at2 in dbContext.Table2 on new { at.SomeField1, at.SomeField2 } equals new { at2.SomeField1, at2.SomeField2 } into at2Group
              from at2Data in at2Group.DefaultIfEmpty()
              select new
              {
                  DepartmentId = department.Id,
                  FieldFromTable1 = at.SomeField,
                  FieldFromTable2 = at2Data.SomeField
              }).ToListAsync();

var result = (from department in Departments
              from employeeId in department.Employees.Select(e => e.EmployeeId)
              join at in dbContext.Table1 on employeeId equals at.EmployeeId
              join at2 in dbContext.Table2 on new { at.SomeField1, at.SomeField2 } equals new { at2.SomeField1, at2.SomeField2 } into at2Group
              from at2Data in at2Group.DefaultIfEmpty()
              select new
              {
                  DepartmentId = department.Id,
                  FieldFromTable1 = at.SomeField,
                  FieldFromTable2 = at2Data.SomeField
              }).ToListAsync();

public class Employee {
            public int Id;
            public string Name;
        }

        public class Department{
            public int Id;
            public List<Employee> Employees;
        }


     public class Table1{
        public int Id;
        public int EmployeeId;
        public string SomeField1;
        public string SomeField2;
        public string SomeField3;
        public string SomeField4;
        public string SomeField5;
        public string SomeField6;
     }

     public class Table2{
        public int Id;
        public string SomeField1;
        public string SomeField2;
        public string SomeField3;
        public string SomeField4;
        public string SomeField5;
        public string SomeField6;
     }

public class Employee {
            public int Id;
            public string Name;
        }

        public class Department{
            public int Id;
            public List<Employee> Employees;
        }


     public class Table1{
        public int Id;
        public int EmployeeId;
        public string SomeField1;
        public string SomeField2;
        public string SomeField3;
        public string SomeField4;
        public string SomeField5;
        public string SomeField6;
     }

     public class Table2{
        public int Id;
        public string SomeField1;
        public string SomeField2;
        public string SomeField3;
        public string SomeField4;
        public string SomeField5;
        public string SomeField6;
     }

49 replies

Gaming

Programming