Selecting employees in certain departments using LINQ
lstdepartment



We don't use Hungarian notation with prefixes like lst in C#. It's just departments



This is notepad coding but you can use a join to get the matching deparment 

var results = 
    from e in _context.Employee
    join d in _context.Department on e.DepartmentId equals d.DepartmentId
    where e.EmployeeId = EmployeeId
    select new EmployeeInfo
    {
        EmployeeId = e.EmployeeId,
        FirstName = e.FirstName,
        LastName = e.LastName,
        MiddleName = e.MiddleName,    
        DepartmentName= d.DepartmentName
    }).ToList();


although this is not yet exactly what you have because this will yield only exact matches. If you want deparment-name to be null then you need a left join and I think this should work:

var results = 
    from e in _context.Employee
    join d in _context.Department on e.DepartmentId equals d.DepartmentId into ds
    from d in ds.DefaultIfEmpty()
    where e.EmployeeId = EmployeeId
    select new EmployeeInfo
    {
        EmployeeId = e.EmployeeId,
        FirstName = e.FirstName,
        LastName = e.LastName,
        MiddleName = e.MiddleName,    
        DepartmentName= d?.DepartmentName
    }).ToList();