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

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?

CodePudding user response：

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

CodePudding user response：

Your code is true bro, but I write and tested these queries and shown the result. its worked for me.

         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) => new {
                    MasterId = m.Id,
                    Category = m.Category,
                    DetailId = d.Id,
                    IsDeleted = d.IsDeleted,
                })
            .ToListAsync();


        var result2 = from m in _dBContext.MyMasters.Include(m => m.MyDetails)
                      join d in _dBContext.MyDetails on m.Id equals d.MasterId
                      where m.Category == "foo" && !d.IsDeleted
                      select new { 
                            MasterId = m.Id,
                            Category = m.Category,
                            DetailId = d.Id,  
                            IsDeleted = d.IsDeleted,
                      };

you try for select New type after Join two collection. IF you get Collection from MyDetails in masters, you have all items because it's not filtered, it's just all navigations item.

CodePudding user response：

It looks like the actual question is how to filter the related entities.

Filtered Includes

In EF Core 5 and later this is done using filtered includes:

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

Global Query Filters

Another option that works on previous versions as well is to use global query filters to ensure deleted entities are never loaded through that DbContext, even by accident :

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyDetail>().HasQueryFilter(p => !p.IsDeleted);
    ...
}

This way, the following query will only load active records :

var result = await dbContext.MyMasters
    .Include(m => m.MyDetails)
    .Where(m => m.Category = 'foo')
    .ToListAsync();

A global query filter affects the entire DbContext which is perfectly fine. A DbContext isn't a model of the database, and entities aren't tables. It's perfectly fine to have different DbContexts with entities and different configurations targeting the same database, that handle specific scenarios/use cases (bounded contexts if you like DDD).

In this case most of the application would use a DbContext with the global query filter(s) while administrative pages would use a different one that allowed access to all records