EF: Get master-detail based on conditions on master as well as on detail

Question

EF Core 3.1 and such master-detail model:

public class MyMaster
{
    public Guid Id { get; set; }
    public string Category { get; set; }
    public List<MyDetail> MyDetails { get; set; }
}

public class MyDetail
{
    public Guid Id { get; set; }
    public Guid MasterId { get; set; }
    public bool IsDeleted { get; set; }
}

I'd like to select all master records in certain category together with details not marked as deleted. Also if all details for particular master are marked as deleted then this master is not returned. Basically I'd like to run such simple select:

select * from MyMaster m
inner join MyDetail d on m.Id = d.MasterId and d.IsDeleted = 0
where m.Category = 'foo'

I try such LINQ method, but it returns also deleted detail records:

var result = await dbContext.MyMasters
    .Include(m => m.MyDetails)
    .Where(m => m.Category = 'foo')
    .Join(dbContext.MyDetails.Where(d => !d.IsDeleted), m => m.Id, d => d.MasterId, (m, d) => m)
    .ToListAsync();

How the LINQ method query should look like?

Answer 1

If you need data just for query, it can be easily retrieved by Select:

var result = await dbContext.MyMasters
    .Where(m => m.Category = 'foo')
    .Select(m => new MyMaster
    {
        Id = m.Id,
        Category = m.Category,
        MyDetails = m.MyDetails.Where(d => !d.IsDeleted).ToList()
    })
    .ToListAsync();