I’m building a query up before running it and trying to search something on the navigation properties. I can do that, but EF brings back everything from that navigation property, when I’d like just the items that actually matched the criteria. I believe that’s due to the use of Any(), but I’m not sure how else to write this query in a way that works. Here’s the data classes (simplified):

public class Record
{
public int Id { get; set; }
public List<Place>? Places { get; set; }
}

public class Place
{
public int Id { get; set; }
public Address? Address{ get; set; }
}
public class Address
{
public int Id { get; set; }
public string? StreetName { get; set; }
}

I start the query like this:

 var searchData = (from r in context.Table
                  select new Record()
                  {
                      Place= r.Places.Select(x => new Place()
                      {
                          Id = x.Id,
                          Address = new Address()
                          {
                              Id = x.Address.AddressId,
                              StreetName = x.Address.StreetName,
                          }
                       }
                   });

Then I’m going through and building up the parameters based on what passed in to make the query. There’s quite a few of these, but the relevant one is street name:

if (someCriteriaToSearchStreetName)
        searchData = searchData.Any(x => x.Place.Where(y => y.Address.StreetName.Contains(aStreetName);

This works in the sense that only Record that match it are returned, but every Address is returned even if it didn’t. eg: if I have 3 addresses and one matches, all 3 return. I want only the matching 1 to return.

I knwo that Any() is probably the source of the problem here, but I’m not sure how else to write this query in a way that works. If I wrote this in SQL as a JOIN I would only get the matching one, but I’m unsure how to translate that to Linq to Entities.

I’m using EF Core 8 and the Oracle Managed Client 21.

Thanks.