Where clause on collection

.net c# entity-framework linq-to-entities

Question

I'm utilizing the BAGA code from the DbContext book by Julie Lerman. I'm having trouble re-creating the following SQL query in LINQ and putting the results in a List collection. 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)

Consequently, in English, compile a list of all USA-related destinations and their associated hotels. Distance from closest airport > 5

Even if the syntax doesn't compile, I was hoping for something like these lines

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

Any thoughts?

1
5
6/23/2018 12:43:45 PM

Accepted Answer

As @Sampath noted, this is typically accomplished using navigation attributes, however I think his solution falls short of your expectations. This is closer, in my opinion:

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

If I strictly adhere to your criterion, it still does not do what you want. You require the address with the accommodations included, or the navigation properties partially filled. This can be useful if the entities need to be serialized and transmitted as a single package to a client (web). (However, the aforementioned technique would also work for that.)

However, a collection with partially loaded navigation attributes is feasible.

The book demonstrates how to partially load a single entity's navigation attributes on page 40 (short for:context.Entry(entity).Collection(e => e.Children).Query().Where(condition) . But as was previously said, that is just one example. For a group of entities, that is not the optimal course of action.

The amusing part is that you can accomplish it quickly for a collection of things by loading the necessary collection elements into the context independently, as one of my colleagues discovered:

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

As you loop through nowcontext.Destinations.Where(d => d.Country == "USA") You'll notice that a lot of people live in their accommodations.>5 Most likely because EF performed relationship fixup at this time. (Lazy loading deactivated; otherwise, the navigation properties will fully load.)

Edit (after your comment)
You are absolutely correct that it is a little bit of a hack. Actually, I was going to add it, but I forgot. The issue is that when lazy loading is accidentally enabled by someone who is unaware of the purpose of the code, the entire mechanism falls apart. Code that depends on the state in an indirect way annoys me. I therefore always favor the initial course of action.

7
6/23/2018 12:32:04 PM

Popular Answer

Typically, via means of navigational qualities are loaded when you acquire the entity to perform this.

However, you may also use the following to accomplish this:

(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 that was helpful.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow