score:2

Accepted answer

You want the RouteInstances in the end? If so:

var query = DataContext.RoutingLocationHistory
           .Where(rlh => rlh.RouteTakenTime >= new System.DateTime(2011,2,4) 
                        && rlh.RouteTakenTime <= new System.DateTime(2011,2,5))
           .Select(rlh => rlh.RoutingLocation.RouteInstanceRule.RouteInstance)
           .Distinct();

score:0

Check for associations in the dbml designer. If it noticed your foreign keys, it will create the associations automatically. If not - you can add them yourself (even when the foreign key does not exist or is not enforced).

By querying the association property, you can hide your relationship details.

query =
  from ri in dataContext.RouteInstances
  from rir in ri.RouteInstanceRules
  from rl in rir.RoutingLocations
  from rlh in rl.RoutingLocationHistories
  where rlh.RouteTakenTime >= new System.DateTime(2011,2,4)
    && rlh.RouteTakenTime <= new System.DateTime(2011,2,5)

If you want similiar results to Guillaume86's answer, but without the distinct... then do this subquery'ing filter.

IQueryable<RouteInstance> query =
   from ri in dataContext.RouteInstances
   where ri.RouteInstanceRules
     .Any(rir => rir.RoutingLocations
       .Any(rl => rl.RoutingLocationHistories
         .Any(rlh => rlh.RouteTakenTime >= new System.DateTime(2011,2,4)
           && rlh.RouteTakenTime <= new System.DateTime(2011,2,5)
         )
       )
     )
   select ri;

score:1

i tested a scenario like yours and got the same results
you may select many without join, sth like:

var query = from ri in RouteInstance
            from rir in RouteInstanceRules 
            from   rl in        RoutingLocation
            from    rlh in RoutingLocationHistory 
             where    ri.RouteInstanceID ==    rir.RouteInstanceID &&
             rir.RoutingLocationID == rl.RoutingLocationID &&
             rl.RoutingLocationID == rlh.RoutingLocationID &&
             rlh.RouteTakenTime >= new System.DateTime(2011,2,4) && rlh.RouteTakenTime <= new System.DateTime(2011,2,5)

Related Query

More Query from same tag