LINQ query help (LINQ to SQL)

Question

Hi,

I'm not really familiar with LINQ, but I'm currently working on an ASP.NET project and using LINQ to SQL as ORM (I'm building a route planning application). I have a problem creating a query the way I need it, so maybe anyone could give me a hint how to achieve this. My current query looks like this:

var results = from lift in db.Lift
              where lift.StartTime > DateTime.Now
                    && lift.Route.Stop.Any(o => o.Location.Name.ToLower().Contains(origin))
                    && lift.Route.Stop.Any(d => d.Location.Name.ToLower().Contains(destination))
              select lift;

This query works and does its work. The extension I would need is the following: each Stop has a field called Order which determines the Stop order in a specific Route. My query should select all Lifts which Route has Stops which match order and destination (thats what the query does so far) AND where the origin Stop has a lower Order number than the destination Stop. Can I do this with LINQ or do I have to filter the results afterwards?

Thanks in advance, Mathias

Answer 1

Off the top of my head.. try something like this (could probably be simplified):

var results = from lift in db.Lift
              let originStop = lift.Route.Stop.
                  FirstOrDefault(o => o.Location.Name.ToLower().Contains(origin))
              let destinationStop = lift.Route.Stop.
                  FirstOrDefault(d => d.Location.Name.ToLower().Contains(destination))
              where lift.StartTime > DateTime.Now
                    && originStop != null
                    && destinationStop != null
                    && originStop.OrderNumber < destinationStop.OrderNumber
              select lift;