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

Question

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

Answer 1

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;