Linq one to many with filter

Question

I have an Entity Framework database that I'm querying, so I'm using linq-to-entities.

Here's my query:

// 'Find' is just a wrapper method that returns IQueryable

var q = r.Find(topic =>
    topic.PageId != null &&
    !topic.Page.IsDeleted &&
    topic.Page.IsActive)

// These are standard EF extension methods, which are used to include linked tables. Note: Page_Topic has a one-to-many relationship with topic.

.Include(topic => topic.Page.Route)
.Include(topic => topic.Page_Topic.Select(pt => pt.Page.Route))

// HERE'S THE QUESTION: This select statement needs to flatten Page_Topic (which it does). But it seems to do it in the wrong place. To explain, if I were to include another column that depended on Page_Topic (for example: 'PillarRoutName2', I'd have to apply the same flattening logic to that column too. Surely the filtering of Page_Topic should be done higher up the query in a DRY way.

.Select(x => new
{
    TopicName = x.Name,
    HubRouteName = x.Page.Route.Name,
    PillarRouteName = x.Page_Topic.FirstOrDefault(y => y.IsPrimary).Page.Route.Name
}).ToList();

Answer 1

Surely the filtering of Page_Topic should be done higher up the query in a DRY way.

Correct! And it's easy to do this:

.Select(x => new
{
    TopicName = x.Name,
    HubRouteName = x.Page.Route.Name,
    FirstTopic = x.Page_Topic.FirstOrDefault(y => y.IsPrimary)
})
.Select(x => new
{
    TopicName = x.TopicName,
    HubRouteName = x.HubRouteName,
    PillarRouteName = x.FirstTopic.Page.Route.Name,
    PillarRoutName2 = x.FirstTopic. ...
}).ToList();

Depending on where you start to get properties from FirstTopic you can also use x.Page_Topic.FirstOrDefault(y => y.IsPrimary).Page or .Page.Route in the first part.

Note that you don't need the Includes. They will be ignored because the query is a projection (Select(x => new ...).