Count() is not working correctly with Include() and HasQueryFilter()

[hakanaltindis]

Hi everybody,

Count() method in EF Core is not working correctly when I called one entity what has global query filter with Include() method.

// These are my entities
public class Customer
{
  public int Id { get; set; }
  public string Name { get; set; }
  public int Age { get; set; }
  public bool IsDeleted { get; set; }

  public List<Activity> Activities { get; set; }
}

public class Activity
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Description { get; set; }
  
  public int CustomerId { get; set; }
  public Customer Customer { get; set; }
}

public class MyDbContext : DbContext
{
  public DbSet<Customer> Customers { get; set; }
  public DbSet<Activity> Activities { get; set; }

  protected override void OnModelCreating(ModeBuilder builder)
  {
    modelBuilder.Entity<Activity>(e => {
      e.HasOne(a => a.Customer)
        .WithMany(c => c.Activities)
        .HasForeignKey(a => a.CustomerId);
    });

    // Made other property configurations....

    /****** THIS POINT IS IMPORTANT ******/
    modelBuilder.Entity<Customer>().HasQueryFilter(c => !c.IsDeleted);
  }
}

And consider records of the tables in database like the below

Customer

Id	Name	Age	IsDeleted
1	Hakan	32	false
2	John	22	false
3	Jane	54	true
4	Chris	26	false

Activity

Id	Name	Description	CustomerId
1	October Fest	bla bla bla...	2
2	Rio Carnaval	bla bla bla...	3
3	Christmas	bla bla bla...	1
4	Easter	bla bla bla...	1

I configured everything well.

When I called like the below

_dbContext.Activities.Include(a => a.Customer).Count();

Query is like that:

Select Count(*) From Activity

If I call like that:

_dbContext.Activities.Include(a => a.Customer).ToList();

The query is like that:

Select a.id, a.Name, c.Name
From Activity as a
	Inner Join Customer As c On (a.CustomerId = c.Id)
Where c.IsDeleted = false

What can I do to solve this problem?

Include provider and version information

EF Core version:
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.4.1

[maumar]

_dbContext.Activities.Include(a => a.Customer).Count(); returns a number of entities in Activities - the include has no influence on that number and is therefore optimized out.

The second query, i.e.:

_dbContext.Activities.Include(a => a.Customer).ToList();

is the one that returns unexpected results from EF perspective - see https://learn.microsoft.com/en-us/ef/core/querying/filters#accessing-entity-with-query-filter-using-required-navigation for more info.

Instead, you can define a query filter on Activity:

modelBuilder.Entity<Activity>().HasQueryFilter(a => !a.Customer.IsDeleted);

this way activities whose customer is deleted will be filtered out in all cases.

[hakanaltindis]

Hi @maumar ,

Thank you for your response.

I already read your shared link before.

I do not suppose I understand clearly. Because there is a sentence like below in your shared link.

The predicate expressions passed to the HasQueryFilter calls will now automatically be applied to any LINQ queries for those types.

And you now say "Global query filters always may not be added". I think there is an inconsistency in here.

Instead, you can define a query filter on Activity:

modelBuilder.Entity<Activity>().HasQueryFilter(a => !a.Customer.IsDeleted);

this way activities whose customer is deleted will be filtered out in all cases.

Finally, about in your suggestion. I saw this method. it worked. But I think, this solution is not sustainable. Because I have to think every combinations between entities while implementing this. And then maybe the queries will run too slow.

Do you have any play to fix it. Or I think your recommendation is that we should not use EF Core and LINQ in enterprise application because of this situation.

[roji]

The same conversation is happening in #19649 (comment), this really is a duplicate.

[roji]

Duplicate of #19649

[hakanaltindis]

You wanted me to open new issue. And then I opened this.

[roji]

@hakanaltindis you're right, that's my fault - I initially understand your comment on #19649 as a new, unrelated issue rather than continuing discussion of the original design. I'll go ahead and close this, and we can continue on that issue.