$count is not working correctly with $expand and EF Core HasQueryFilter

Hi,

My situation may be rare topic. I did not find anything about that. I try to tell with a sample.

// 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. The web application perfectly run with odata. But some conditions return wrong value.

Let's tell you that,

I called the below query to show on a list.

http://localhost:5000/odata/activity?$expand=customer($select=name)&$select=id,name&$top=10&$count=true

Response to the request is the below:

{
  "@odata.context":"http://localhost:5000/odata/$metadata#Activity(id,name,customer(name))",
  "@odata.count":4,
  "value":[
    {
      "id": 1,
	  "name": "October Fest",
	  "customer": {
		"name": "John"
	  }
    },
	{
      "id": 3,
	  "name": "Christmas",
	  "customer": {
		"name": "Hakan"
	  }
    },
	{
      "id": 4,
	  "name": "Easter",
	  "customer": {
		"name": "Hakan"
	  }
    }
  ]
}

It returns an array with 3 items but @odata.count is 4. This is the my issue.

You know that, OData call 2 SQL Query for this url. First one to get data. Second one to get count. I checked SQL Queries in console.

First Query 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

Second Query like that:

Select Count(*) From Activity

Why do OData not join $expand to SQL query for getting count?

My example may be not valuable. But you can think this issue with authorization.

In addition, this issue break my pagination. Because I consider count of pages according to @odata.count

How can I solve this problem?
What can I do?
Do you think I did something wrong?

Start by reproducing the OData query at /odata/activity with $expand, $select, $top, and $count against the EF Core model using HasQueryFilter. Compare the generated data and count SQL, then trace the $count and $expand query-processing paths. Done means @odata.count reflects the filtered result set and pagination remains correct.