Need a lambda expression to have an include with a where clause

Question

I need to get a list of Decades that has a list of Songs.

The kicker is I need a list of ACTIVE Decades along with ACTIVE songs.

Here are my models:

Here is what I have so far:

Decades = await _context.Decades
                .Include(x => x.Songs)
                .Where(x => x.Active == true && x.Songs.Any(s => s.Active == true))
                .OrderBy(x => x.DecadeId)
                .ToListAsync()

But what I get are Decades that are ACTIVE but the Songs don't matter - I get them all no matter the ACTIVE flag.

Thoughts?

Here is the solution I came up with - with the help from Jawad below - for those that may find it useful:

           Decades = await _context.Decades
                .Include(x => x.Songs)
                .Where(x => x.Active == true)
                .OrderBy(x => x.DecadeId)
                .Select(x => new Decade()
                {
                    DecadeId = x.DecadeId,
                    DecadeText = x.DecadeText,
                    DecadeExtended = x.DecadeExtended,
                    Description = x.Description,
                    Active = x.Active,
                    Added = x.Added,
                    Songs = x.Songs
                        .Where(s => s.Active == true).ToList()
                }).ToListAsync()

There is no provision in EF in a single query for you to filter what is included. See [this answer](https://stackoverflow.com/a/39641553/2557128). — NetMage, Jan 09 '20 at 00:31

score 0 · Accepted Answer · answered Jan 09 '20 at 00:52

0

You wont be able to filter out a list thats brought in via include statement. You will need use Select statement to get the data with sub lists filtered the way you want. Something like this,

    Decades = await _context.Decades
                .Include(x => x.Songs)
                .Where(x => x.Active)
                .OrderBy(x => x.DecadeId)
                .Select(x => new Decade() { 
                   x.DecadeId,
                   ...
                   x.Added,
                   Songs = x.Songs.Where(x => x.Active)
                 })
                .ToListAsync();

answered Jan 09 '20 at 00:52

Jawad

11,028
3
24
37

This answer helped me the most. I was able to adjust and get things correct – Mike Henry Jan 09 '20 at 14:20
I tried this solution but resulted in an exception - see https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query – Pilgerstorfer Franz Dec 08 '20 at 12:14

score 0 · Answer 2 · answered Jan 09 '20 at 00:53

You can get all active decades, then get all active songs and combine enumerables per your need.

It won't take more time as Join operation is quite an expensive one if it fires on the database side.

var decades = await _context.Decades
            .Where(x => x.Active)
            .ToListAsync();
var songs = await _context.Songs
            .Where(x => x.Active)
            .ToListAsync();
return decades.Select(x => new { Decade = x, Songs = songs.Where(s => s.DecadeId == x.Id)});

It probably can be rewritten more efficiently with GroupBy or something, but it should work.

Need a lambda expression to have an include with a where clause

2 Answers2