How do you specify a where clause on data returned from a .Include in an Entity Framework query?

Given the following database table hierarchy:

Region
------
RegionId
RegionName

Country
-------
CountryId
RegionId
CountryName

Destination
-----------
DestinationId
CountryId
DestinationName

Venue
-----
VenueId
DestinationId
VenueName

I have the following Entity Framework query:

var result = from region in context.Region.Include("Country.Destination.Venue") 
select region

which will return all rows from all tables (outer join)

Is it possible to introduce a where clause so that only rows where the venue is not null are included (or use an inner join) ?

Thanks

From stackoverflow

• Try this. It should return the results you are looking for: only regions that have a corresponding venue.

      var result = from region in context.Region.Include(Country.Destination.Venue)
                   let v = (from ctry in region.Country
                           join dest in context.Destination
                           on ctry.CountryId
                           equals dest.CountryId
                           into destGroup
                     from dests in destGroup
                     join ven in context.Venue
                     on dests.DestinationId
                     equals ven.DestinationId
                     into venGroup
                           select ctry).Any()
                   where v == true
                   select region;
  

  Alan Heywood : Thanks, I tried this however it brings back all regions in the case that there is an associated Country and Destination but no associated Venue.

  YeahStu : Ok. You might be right. I tested this on a sample app here but I only had three tables to play with, not four. I hope this shows an example of what you might try though.