LINQ to Entities(实体框架)Join和.include冲突

问题描述

我想做的事情似乎很简单.我想选择一些雇主，并且要包括按年份和季度降序排序的最近6个季度数据记录.

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.

考虑表达式:

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

我走上了正确的道路，因为我得到了我想要的7个雇主记录，并且每个记录都包含所有季度数据.现在，我要做的就是对数据进行排序，并仅选择前6条记录.

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.

此表达式完成顺序，但不限制为6.

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;

上面的查询还具有两个不希望有的副作用.现在，我获得了208条记录，而不是原来的7条记录，并且我也不再获得任何EmployerQuarterly数据！

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!

我不想牺牲我的渴望.我对L2E的要求是什么?

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

推荐答案

您不能限制关系，因为EF不会加载部分实现的实体.因此，如果要加载相关数据的子集，则需要投影到POCO上，而不是加载实体.即:

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)
            };

因为要投影，所以不再需要Include().

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

这篇关于LINQ to Entities(实体框架)Join和.include冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！