How do I specify a where clause for data returned from a .Include query in Entity Framework?
Given the following hierarchy of database tables:
Region
------
RegionId
RegionName
Country
-------
CountryId
RegionId
CountryName
Destination
-----------
DestinationId
CountryId
DestinationName
Venue
-----
VenueId
DestinationId
VenueName
I have the following Entity Framework request:
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 to include only rows where the meeting place is not null (or use an inner join)?
thank
+1
source to share
1 answer
Try it. It should return the results you are looking for: only the regions that have the matching location.
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;
+1
source to share