Entity Framework延迟加载如何添加isDeleted过滤约束？

Great question! This is such a common scenario when working with soft-deleted data (using an IsDeleted flag) — you want to make sure related entities only return active (non-deleted) records directly from the database, instead of filtering them in memory after the fact. Let's break down your options based on which EF version you're using:

Option 1: EF Core (Recommended) - Global Query Filters #

EF Core has a built-in feature called query filters that lets you define a default filter for an entity type, which applies automatically to all queries (including Include calls). This is perfect for soft-deleted data because it ensures you never accidentally pull deleted records unless you explicitly opt out.

Here's how to set it up in your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Add a global filter for the Document entity to exclude deleted records
    modelBuilder.Entity<Document>()
        .HasQueryFilter(document => !document.IsDeleted);
}

Now when you run:

var allCustomers = db.Customers.Include(c => c.Documents).ToList();

The generated SQL will automatically include a WHERE IsDeleted = 0 (or equivalent) clause for the Documents join, so only non-deleted documents are loaded with each customer.

If you ever need to bypass the filter (e.g., for an admin view that shows deleted records), you can use IgnoreQueryFilter():

var allDocumentsIncludingDeleted = db.Documents.IgnoreQueryFilter().ToList();

Option 2: EF6 (Older Version) #

EF6 doesn't have built-in query filters, but you still have ways to filter related entities at the database level:

Sub-option 2a: Project to a DTO or Anonymous Type #

Instead of using Include, you can project the data you need, which lets you filter the related documents directly in the query:

var customerWithActiveDocs = db.Customers
    .Select(c => new 
    {
        // Include all customer properties you need
        CustomerId = c.Id,
        Name = c.Name,
        // Filter the related documents here
        Documents = c.Documents.Where(d => !d.IsDeleted)
    })
    .ToList();

This generates a SQL query that joins customers with only non-deleted documents, so the database only returns the data you want. If you need to map back to your entity type, you can do that after fetching the results.

Sub-option 2b: Explicit Loading with Filtering #

If you prefer to keep using your entity types directly, you can load the related documents explicitly with a filter:

// First load all customers
var customers = db.Customers.ToList();

// Then explicitly load only active documents for each customer
foreach (var customer in customers)
{
    db.Entry(customer)
        .Collection(c => c.Documents)
        .Query()
        .Where(d => !d.IsDeleted)
        .Load();
}

This makes a separate database call for each customer's documents (so watch out for N+1 queries if you have a lot of customers), but each query only fetches non-deleted records.

Sub-option 2c: Custom DbSet or Extension Method #

You can create a custom property in your DbContext that returns only active documents, then use that in your queries:

public class YourDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Document> Documents { get; set; }

    // Custom set for active documents
    public IQueryable<Document> ActiveDocuments
    {
        get { return Documents.Where(d => !d.IsDeleted); }
    }
}

While this doesn't directly work with Include, you can combine it with projection or explicit loading to get the filtered related data.

Key Takeaway #

If you're on EF Core, global query filters are the cleanest, most maintainable solution — they ensure consistency across all your queries without extra code. For EF6, projection or explicit loading are your best bets to filter at the database level.

内容的提问来源于stack exchange，提问作者Kevin Hecker