How to filter GraphQL query result by joined table column (field)

Question

I am trying to filter GraphQL query result by joined table column (field). Relationship between tables is Many-to-Many (with bridge table of course) and I want to filter Advertisements by the Cities related to them.

I am using C#, .NET 6.0 and EF Core with HotChocolate NuGet package for GraphQL. Just a reminder, filtering by main table columns works just fine. Here is how my Query class looks like:

And here are the queries I tried:

Here are them typed:

{
  advertisements(where: { and: [{ type: { eq: SERVICE } }, {or: [{cities.name: { {eq:"London"} }}]}] }) {
    title
    id
    owner {
      firstName
      lastName
    }
    description
    rateType
    price
    categories {
      name
    }
    cities {
      name
    }
  }
}

---

{
  advertisements(where: { and: [{ type: { eq: SERVICE } }, {or: [{cities: {name: {eq:"London"}} }]}] }) {
    title
    id
    owner {
      firstName
      lastName
    }
    description
    rateType
    price
    categories {
      name
    }
    cities {
      name
    }
  }
}

---

{
  advertisements(where: { type: { eq: SERVICE } }) {
    title
    id
    owner {
      firstName
      lastName
    }
    description
    rateType
    price
    categories {
      name
    }
    cities(where: {name: {eq: "London"}}) {
      name
    }
  }
}

Answer 1

The filter statements has to be in "advertisements"

advertisements(where:{ type: { eq: SERVICE }} cities: {name: {eq: "London"}})

Also, in your Query class you can remove the include statements and instead use [UseProjection]

[UseProjection]
[UseFiltering]
public IQueryable<Advertisement> GetAdvertisements([Service] HomyDbContext context) => context.Advertisement;