LINQ to Entities (Entity Framework) Join and Enable 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.

Consider the expression:

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

I am on the right track because I get 7 Employer records that I wanted, and each of them has all the quarterly data. Now all I have to do is order the data and select only the first 6 records.

This expression does order, but not limit 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; 

There are also two unwanted side effects in the above request. I am now returning 208 records, not my original 7s And I am no longer returning EmployerQuarterly data!

I do not want to sacrifice my energetic load. Is what I'm asking for an opportunity with L2E?

Answer 1

You cannot restrict communication because EF will not load a partially materialized object. Therefore, if you want to load a subset of the related data, you need to design in POCOs rather than loading objects. 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) }; 

Since you are projecting, you no longer need Include() .