sub linq query is making this take a very long time, how can I make this faster?

Question

I have a list of employees that I build like this:

var employees = db.employees.Where(e => e.isActive == true).ToList();

var latestSales = from es in db.employee_sales.Where(x => x.returned == false);

Now what I want is a result like this:

int employeeId
List<DateTime> lastSaleDates

So I tried this, but the query takes a very very long time to finish:

var result = 
  (from e in employees
   select new EmployeeDetails
   {
      EmployeeId = e.employeeId,
      LastSaleDates = 
           (from lsd in latestSales.Where(x => x.EmployeeId == e.EmployeeId)
                                   .Select(x => x.SaleDate)
            select lsd).ToList()
   };

The above works, but literally takes 1 minute to finish.

What is a more effecient way to do this?

Answer 1

You can use join to get all data in single query

var result = from e in db.employees.Where(x => x.isActive)
             join es in db.employee_sales.Where(x => x.returned)
                  on e.EmployeeId equals es.EmployeeId into g
             select new {
                  EmployeeId = e.employeeId,
                  LastSaleDates = g.Select(x => x.SaleDate)
             };

Unfortunately you can't use ToList() method with Linq to Entities. So either map anonymous objects manually to your EmployeeDetails or change LastSalesDates type to IEnumerable<DateTime>.

Answer 2

Your calls to ToList are pulling things into memory. You should opt to build up a Linq expression instead of pulling an entire query into memory. In your second query, you are issuing a new query for each employee, since your are then operating in the Linq-to-objects domain (as opposed to in the EF). Try removing your calls to ToList.

You should also look into using Foreign Key Association Properties to makes this query a lot nicer. Association properties are some of the most powerful and useful parts of EF. Read more about them here. If you have the proper association properties, your query can look as nice as this:

var result = from e in employees
      select new EmployeeDetails
    {
        EmployeeId = e.employeeId,
        LastSaleDates = e.AssociatedSales
    }

You might also consider using a join instead. Read about Linq's Join method here.