I have 3 tables in postgres DB (docker postgres:14.1-alpine).

public  class House
{
    public Guid Id { get; set; }
    
    public virtual ICollection<Flat> Flats { get; set; }
    public HouseType Type { get; set; }
    
}

public  class Flat
{
    public Guid Id { get; set; }
    public  House House { get; set; }
}

public  class HouseType
{
    public Guid Id { get; set; }
    public Guid IdMaterils { get; set; }
    public virtual ICollection<House> Houses { get; set; }
}

I want to find houses with material type = “Some value guid” and flats whose IDs are equal “Other some value guid”. Houses with empty collection flats must be excluded

For example tables contain this data:

Houses:

Id                                  |TypeId                              |
------------------------------------+------------------------------------+
dbec9670-46ef-47a0-8727-4a3c24a0f2ec|fdd1a587-9dc2-4c02-a375-39bdf3de775e|
e912d942-8d9c-4511-b0f8-6e3ac78bb8d2|fdd1a587-9dc2-4c02-a375-39bdf3de775e|
fd6f29ac-70aa-44ef-9257-c204a954f4ad|fdd1a587-9dc2-4c02-a375-39bdf3de775e|

HuseTypes:

Id                                  |IdMaterils                          |
------------------------------------+------------------------------------+
fdd1a587-9dc2-4c02-a375-39bdf3de775e|85b7fb0e-4f8f-4d71-8c4b-d763ef5c767d|
0f99f64d-14bf-4732-b7cd-2e710889b20a|40744e83-b31a-4a62-81d0-9ad05f318aa2|

Flats:

Id                                  |HouseId                             |
------------------------------------+------------------------------------+
077fd0e3-af9d-468f-8209-3cb6bfc1cad1|dbec9670-46ef-47a0-8727-4a3c24a0f2ec|
0185c3d3-b18b-4616-a339-f1278c1ab46e|dbec9670-46ef-47a0-8727-4a3c24a0f2ec|
94c93b29-3bce-44b7-a97c-b490b47f78b7|e912d942-8d9c-4511-b0f8-6e3ac78bb8d2|

I run query:

var result = await _bookingContext.Houses             
    .Include((x => x.Flats.Where(x => x.Id == new Guid("077fd0e3-af9d-468f-8209-3cb6bfc1cad1"))))             
    .Include(x => x.Type)             
    .Where(x => x.Type.IdMaterils == new Guid("85b7fb0e-4f8f-4d71-8c4b-d763ef5c767d"))             
    .Where(x => x.Flats.Any()).ToListAsync();

Generated SQL:

SELECT h."Id", h."TypeId", h0."Id", t."Id", t."HouseId", h0."IdMaterils"
FROM "Houses" AS h
INNER JOIN "HouseTypes" AS h0 ON h."TypeId" = h0."Id"
LEFT JOIN (
    SELECT f0."Id", f0."HouseId"
    FROM "Flats" AS f0
    WHERE f0."Id" = '077fd0e3-af9d-468f-8209-3cb6bfc1cad1'
) AS t ON h."Id" = t."HouseId"
WHERE h0."IdMaterils" = '85b7fb0e-4f8f-4d71-8c4b-d763ef5c767d' AND EXISTS (
    SELECT 1
    FROM "Flats" AS f
    WHERE h."Id" = f."HouseId")
ORDER BY h."Id", h0."Id"

Simplified result :

houseid                             |flatid                              |
------------------------------------+------------------------------------+
dbec9670-46ef-47a0-8727-4a3c24a0f2ec|077fd0e3-af9d-468f-8209-3cb6bfc1cad1|
e912d942-8d9c-4511-b0f8-6e3ac78bb8d2|                                    |

What needs to be added in request to make the “t.Id is not null” condition appear?

The code not exclude records where left join result is null.
This condition “Where(x => x.Flats.Any()) ” is not work
How do it?

I expected the result to only be houses that have at least an flat with ID = ‘077fd0e3-af9d-468f-8209-3cb6bfc1cad1′ and a house of type with IdMaterials = ’85b7fb0e-4f8f-4d71-8c4b-d763ef5c767d’