EF: Include with where clause

c# entity-framework include where-clause

Question

As the title suggests, I'm trying to figure out how to combine an include with a where clause.

Here are my circumstances: I am in charge of maintaining a sizable application with a lot of bad code. I'm trying to find the safest course of action because changing too much code leads to issues everywhere.

Imagine that I have the objects Bus and People (Bus has a navigation prop Collection of People). I need to choose all the buses in my query but only the awake passengers. This is a straightforward dummy case.

In the present code:

var busses = Context.Busses.Where(b=>b.IsDriving == true);
foreach(var bus in busses)
{
   var passengers = Context.People.Where(p=>p.BusId == bus.Id && p.Awake == true);
   foreach(var person in passengers)
   {
       bus.Passengers.Add(person);
   }
}

Following this code, the context is destroyed, and the resulting bus entities are mapped to a DTO class in the calling function (100 percent copy of Entity).

This code makes numerous calls to the database, which is not acceptable, thus I discovered this fix: BLOGS MSDN

When debugging the outcome, this worked perfectly, however when the entities are mapped to the DTO (using AutoMapper), I receive an exception informing me that the Context/Connection has been terminated and the object can't be loaded. (Context is closed and cannot be changed ( )

Therefore, I must confirm that the Selected Passengers have been loaded (IsLoaded on navigation property is also False). The Count also throws the exception when I check the Passengers collection, however there is also a collection on the Passengers collection named "oewrapped related entities" that contains my filtered objects.

Can these wrapped related entities be loaded into the entire collection? (I am unable to alter the automapper mapping configuration because it is used throughout the entire application.)

Exists a different way to obtain the Active Passengers?

Any advice is appreciated.

Edit

Gert Arnold's response is ineffective because the data isn't loaded immediately. However, when I make it simpler and remove the loading location. This is quite odd because in both situations, run sql returns all of the passengers. It follows that there must be an issue when the results are returned to the entity.

Context.Configuration.LazyLoadingEnabled = false;
var buses = Context.Busses.Where(b => b.IsDriving)
        .Select(b => new 
                     { 
                         b,
                         Passengers = b.Passengers
                     })
        .ToList()
        .Select(x => x.b)
        .ToList();

Edit2

After much effort, Gert Arnold's solution was successful! You must turn off lazy loading, as Gert Arnold advised, and keep it off. Since the previous developer adored Lazy Loading, this will necessitate some more adjustments to the application - -

2
53
5/29/2013 8:46:18 AM

Accepted Answer

This feature is currently introduced to the core of Entity Framework, A breaking story.

The necessary objects can be requested using

Context.Configuration.LazyLoadingEnabled = false;
// Or: Context.Configuration.ProxyCreationEnabled = false;
var buses = Context.Busses.Where(b => b.IsDriving)
            .Select(b => new 
                         { 
                             b,
                             Passengers = b.Passengers
                                           .Where(p => p.Awake)
                         })
            .AsEnumerable()
            .Select(x => x.b)
            .ToList();

Here, you first retrieve from the database the buses that are now in motion and the passengers that are awake. Then,AsEnumerable() buses and passengers will be materialized and then processed in memory as a result of the migration from LINQ to Entities to LINQ to Objects. Without it, EF will only realize the final projection, therefore it is crucial.Select(x => x.b) not the travelers.

EF now provides a functionality called fixing relationships that handles creating associations between all materialized objects in the context. As a result, for eachBus Only those that are awake are currently aboard.

When you arrive to the bus collection byToList With the help of AutoMapper, you may map the buses that carry the desired people.

Only when lazy loading is deactivated does this function. Otherwise, when the passengers are accessible during the conversion to DTOs, EF would lazily load all passengers for each bus.

Lazy loading can be disabled in two different methods. DisablingLazyLoadingEnabled will turn on lazy loading once it is enabled once more. DisablingProxyCreationEnabled will produce entities that lack the ability to perform lazy loading, therefore they won't begin doing so afterProxyCreationEnabled is reactivated. When the context lasts longer than just one particular question, this can be the best option.

However, many-to-many

This workaround, as said, depends on relationship repair. However, relationship fixup doesn't function with many-to-many associations, as stated by here and Slauma. IfBus -Passenger the only thing you can do is change it yourself because it is many-to-many:

Context.Configuration.LazyLoadingEnabled = false;
// Or: Context.Configuration.ProxyCreationEnabled = false;
var bTemp = Context.Busses.Where(b => b.IsDriving)
            .Select(b => new 
                         { 
                             b,
                             Passengers = b.Passengers
                                           .Where(p => p.Awake)
                         })
            .ToList();
foreach(x in bTemp)
{
    x.b.Pasengers = x.Passengers;
}
var busses = bTemp.Select(x => x.b).ToList();

...and everything starts to look even less desirable.

external resources

Zzz-109-zzz is a library that makes this process more simpler. It enables you to create entity-specific global filters that will be used each time the entity is accessed. In your situation, this might appear as:

modelBuilder.Filter("Awake", (Person p) => p.Awake, true);

Then, if you...

Context.Busses.Where(b => b.IsDriving)
       .Include(b => b.People)

...you'll observe that the included collection has the filter applied.

Filters can also be enabled or disabled, giving you control over when they are used. This library is really cool, in my opinion.

The creator of AutoMapper has a comparable package called EntityFramework.Filters.

Core Entity Framework

There are filters for all queries in EF-core since version 2.0.0. Although this is a wonderful addition to its features, the restriction so far is that a filter can only contain references to the root entity of a query and not to navigation properties. Hopefully, these filters will be used more frequently in future versions.

Filtered includes have long been requested as a feature. The EF-core problem is located at here.

61
3/25/2020 7:01:34 PM

Expert Answer

I am the project's owner, which is Disclaimer.

Filtering related items is possible using the EF+ Query IncludeFilter functionality.

var buses = Context.Busses
                   .Where(b => b.IsDriving)
                   .IncludeFilter(x => x.Passengers.Where(p => p.Awake))
                   .ToList();

Query IncludeFilter in EF+ in Wiki

24
8/18/2018 4:13:48 PM


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