Where clause on collection

Question

I'm using the BAGA code from Julie Lerman's DbContext book. I want to recreate the following SQL query in LINQ and put the results in a List collections and am having problems. http://learnentityframework.com/downloads/

SELECT * FROM baga.Locations d
LEFT JOIN Lodgings l ON d.LocationID = l.destination_id
WHERE d.Country = 'usa'
AND (l.MilesFromNearestAirport > 5 or l.MilesFromNearestAirport is null)

So, in English, get all locations (destinations) that are in the USA and include all the related lodgings where MilesFromNearestAirport > 5

The syntax doesn't compile but I was hoping for something similar to below

var dests = context.Destinations
  .Where(d => d.Country == "USA" && d.Lodgings.Where(l => l.MilesFromNearestAirport > 5))
  .Select(d => d)
  .ToList();

Any ideas?

Solution 1

As @Sampath says, this is normally done with navigation properties but I believe that his code does not exactly do what you want. This (I think) is closer:

var dests = context.Destinations
           .Where(d => d.Country == "USA")
           .Select(d => 
               new { d,
                     RemoteLodgings = d.Lodgings
                         .Where(l => l.MilesFromNearestAirport > 5)}
           .ToList();

But it still does not do what you want if I take your requirement to the letter. You want the location with the lodgings included, i.e. the navigation properties loaded partly. This can be convenient when the entities are to be serialized and sent to a (web) client in one package. (Although the method above would be OK for that too).

But it is possible to make a collection with partly loaded navigation properties.

The book, at page 40, shows how you can partly load navigation properties of a single entity (in short: context.Entry(entity).Collection(e => e.Children).Query().Where(condition). But as said, that's only one instance. It's not the best method to do that for a collection of entities.

The funny thing is (as a colleague of mine found out), you can easily do it for a collection of entities by loading the required collection elements into the context separately:

var lodgings = context.Lodgings
              .Where(l => l.MilesFromNearestAirport > 5 
                       && l.Destination.Country == "USA")
              .ToList();

Now if you loop through context.Destinations.Where(d => d.Country == "USA") you will see that their lodgings are loaded with the ones ">5". Probably because at this point EF executed relationship fixup. (Lazy loading disabled, because lazy loading will fully load the navigation properties).

Edit (after your comment)
I couldn't agree more when you say it is a bit of a hack. Actually, I forgot to mention that in the first place. The problem is that the whole mechanism collapses when lazy loading happens to be activated by someone who's not aware of the what the code is for. I don't like code that depends on the state in a way that is not obvious. So I would always prefer the first approach.

Solution 2

Normally this is done using navigation properties which are loaded when you get the entity.

However you can also do this with the following:

(from d in baga.Locations
 from l in Lodgings
 where (d.LocationID == l.destination_id)
 where (d.Country = 'usa' && (l.MilesFromNearestAirport > 5 || l.MilesFromNearestAirport == null))
 select d)
 .ToList();

I hope this will help to you.

Solution 3

How about using the LINQ join?

 var res = from d in context.Destinations
           join l in context.Lodgings on d.LocationID equals l.destination_id
           where (l.MilesFromNearestAirport > 5 || l.MilesFromNearestAirport == null)
                 && d.Country = "usa"
           select new {
                     Destination = d,
                     Location = l
                  }