LINQ to Entities (Entity Framework) Join and .Include conflict

Question

What I want to do seems pretty simple. I want to select some employers and I want to include the last 6 quarterly data records sorted by year and quarter descending.

Consider the Expression:

var query = from e in data.Employer.Include("EmployerQuarterly")
            where e.UIAccount == 22
            select e;

I'm on the right track because I get the 7 Employer records I wanted and each of those have all of the quarterly data. Now all I have to do is order that data and select only the top 6 records.

This expression accomplishes the order by, but not the limit of 6.

var query = from e in data.Employer.Include("EmployerQuarterly")
            from q in e.EmployerQuarterly
            where e.UIAccount == 22
            orderby q.Year descending, q.Quarter descending
            select e;

The query above also has two undesired side-effects. I now get back 208 records rather than my original 7 AND I no longer get back any EmployerQuarterly data!

I don't want to sacrifice my eager loading. Is what I am asking for possible with L2E?

Answer 1

You can't restrict a relationship, because the EF won't load partially-materialized entity. So if you want to load a subset of the related data, you need to project onto POCOs rather than load entities. I.e.:

var query = from e in data.Employer
            where e.UIAccount == 22
            select new
            {
                Id = e.Id,
                Name = e.Name,
                // etc.
                Quarterlies = (from q in e.EmployerQuarterly
                               orderby q.Year descending, q.Quarter descending
                               select new
                               {
                                   Id = q.Id,
                                   // etc.
                               }).Take(6)
            };

Because you're projecting, you no longer need the Include().