Accepted answer

What are you asking for is not natively supported, so there is no easier way, but for sure there is more efficient way because your current code is performing N + 1 database queries.

A better way could be to retrieve employees and related filtered payments with one database query using anonymous type projection, and then do something similar to your approach to create the final result in memory. For instance:

var listing = 
    db.Employees.Select(employee => new
        payments = employee.Payments.Where(p => p.IsPosted == 1)
    .AsEnumerable() // Switch to LINQ to Objects
    .Select(r =>
        r.employee.Payments = r.payments.ToList();
        return r.employee;


Try something like this: It will give you a list of anonymous type that will hold the employee and it's payments.

using (dbcontext ctx = new dbcontext())

    var result = (from e in ctx.Employees
                  join p in ctx.Payments on e.employeeId equals p.employeeId
                  where p.IsPosted == 1
                  select new
                      Employee = e,
                      Payments = p



it colud be a good alternative

var listing = from d in db.Payments
              .where d.IsPosted == 1
               select d.Employees;

(not tested, then please fix mistakes)

start from pyaments, filter for is posted=1 , then select related emplyees

Related Query

More Query from same tag