ASP.Net Core OData - filter by number of nested matching

Question

I have a root Company entity with CompanyType children. I want to filter companies that have all provided types. It's possible to create such filter in pure SQL

SELECT Companies.Id, Companies.Name
FROM Companies
JOIN (
  SELECT CompanyTypes.CompanyId FROM CompanyTypes
  WHERE CompanyTypes.CompanyTypeId IN (123, 456, 789, 555)
  GROUP BY CompanyId
  HAVING COUNT(*) = 4
) Types ON Companies.ID = Types.CompanyID

It's also possible to create such filter using linq to sql

var typeIds = new[] { 123, 456, 789, 555 };
var companies = await EFContext.Companies.Where(company => company.CompanyTypes.Count(type => typeIds.Contains(type.CompanyTypeId)) == typeIds.Length).ToListAsync();

Is there a way to compose such query using odata syntax? I've tried this

companyTypeIds/any(i: i eq 123) and companyTypeIds/any(i: i eq 456) and companyTypeIds/any(i: i eq 789) and companyTypeIds/any(i: i eq 555)

It gives expected result but underlying query is different and it becomes very slow when number of conditions grows. Is there a way to make it more efficient like provided SQL queries? The backend is using ASP.Net Core 6, EF Core 6, ASP.Net Core OData 8 (latest at the moment).

Answer 1

Having the SQL and the LINQ that you want to translate into an OData query makes this a much easier proposition, the WHERE IN () combined with HAVING COUNT(*) = 4 can both be implemented in OData v4.01 and recently that support has been improved

Adding support for $count segment in $filter collections in OData WebAPI
In OData core v7.9.0 we added improved support for $count segment in $filter collection properties.

$filter=navProp/$count($filter=prop gt 1) gt 2
$filter=collectionProp/$count($filter=prop gt 1) gt 2

For this LINQ:

var typeIds = new[] { 123, 456, 789, 555 };
var companies = await EFContext.Companies.Where(company => 
company.CompanyTypes.Count(type => typeIds.Contains(type.CompanyTypeId)) == 
typeIds.Length).ToListAsync();

The following should work for your query:

~/Companies?$filter=CompanyTypes/$count($filter=CompanyTypeId in (123, 456, 789, 555) eq 4)