.NET CORE 2 EF Include

Question

I am using new .net core and EF.

I need help with include linq command. I have some 1:N models and if the collection contais some data marked like deleted I do not want to include them.

How to do it?

var company = await _context.Company
                .Include(y => y.Administrators)
                .Include(y => y.CompanyPartTimers)
                .Include(z => z.WorkPlaces)
                .Include(z => z.Requirements)
                .FirstAsync(x => x.Id == id);

If I add the condition

.Include(z => z.WorkPlaces).Where(x=>x.WorkPlaces.Where(x=>!x.IsDeleted))

It doesn't work. How to write this correctly?

Next thing is I have IDeletable Interface and it would be better if I had some custom linq expression and could do for ex.

.Include(z => z.WorkPlaces).GetNonDeleted()

Does anyone know how to do it? I tryed something like this

public static class LinqExtension
    {
        public static IEnumerable<T> GetActive<T>(this IEnumerable<T> source) where T : class, IDeletable
        {
            return source.Where(x => x.IsDeleted);
        }
    }

Thanks guys.

Answer 1

You can configure a Query Filter in your DbContext.

modelBuilder.Entity<Administrator>()
            .HasQueryFilter(admin => !EF.Property<boolean>(admin, "IsDeleted"));

should do the trick

Reference link: https://docs.microsoft.com/en-us/ef/core/querying/filters

Answer 2

You should change the inner condition using Any instead of Where, as:

.Include(z => z.WorkPlaces)
.Where(x => x.WorkPlaces.Any(y => !y.IsDeleted))