score:6

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
    {
        employee,
        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;
    })
    .ToList();

score:0

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())
{
    ctx.Connection.Open();

    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
                  }).ToList();

    ctx.Connection.Close();
}

score:1

it colud be a good alternative

var listing = from d in db.Payments
              .include("Employees")
              .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