EF: Include with where clause [duplicate]
As the title suggest I am looking for a way to do a where clause in combination with an include.
Here is my situations: I am responsible for the support of a large application full of code smells. Changing too much code causes bugs everywhere so I am looking for the safest solution.
Let's say I have an object Bus and an object People(Bus has a navigation prop Collection of People). In my Query I need to select all the Busses with only the Passengers that are awake. This is a simplistic dummy example
In the current 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);
}
}
After this code the Context is disposed and in the calling method the resulting Bus entities are Mapped to a DTO class (100% copy of Entity).
This code causes multiple calls to DB which is a No-Go, so I found this solution ON MSDN Blogs
This worked great when debugging the result but when the entities are mapped to the DTO (Using AutoMapper) I get an exception that the Context/Connection has been closed and that the object can't be loaded. (Context is always closed can’t change this :( )
So I need to make sure that the Selected Passengers are already loaded (IsLoaded on navigation property is also False). If I inspect the Passengers collection The Count also throws the Exception but there is also a collection on the Collection of Passegers called “wrapped related entities” which contain my filtered objects.
Is there a way to load these wrapped related entities into the whole collection? (I can't change the automapper mapping config because this is used in the whole application).
Is there another way to Get the Active Passengers?
Any hint is welcome...
Edit
Answer of Gert Arnold doesn't work because the data isn't loaded eagerly. But when I simplify it and delete the where it is loaded. This is realy strange since the execute sql returns all the passengers in both cases. So there must be a problem when putting the results back into 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 a lot of struggle the answer of Gert Arnold work! As Gert Arnold suggested you need to disable Lazy Loading and Keep it OFF. This will ask for some extra changes to the appliaction since the prev developer loved Lazy Loading -_-
This feature has now been added to Entity Framework core 5. For earlier versions you need a work-around (note that EF6 is an earlier version).
Entity Framework 6 work-around
In EF6, a work-around is to first query the required objects in a projection (new) and let relationship fixup do its job.
You can query the required objects by
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();
What happens here is that you first fetch the driving buses and awake passengers from the database. Then, AsEnumerable() switches from LINQ to Entities to LINQ to objects, which means that the buses and passengers will be materialized and then processed in memory. This is important because without it EF will only materialize the final projection, Select(x => x.b), not the passengers.
Now EF has this feature relationship fixup that takes care of setting all associations between objects that are materialized in the context. This means that for each Bus now only its awake passengers are loaded.
When you get the collection of buses by ToList you have the buses with the passengers you want and you can map them with AutoMapper.
This only works when lazy loading is disabled. Otherwise EF will lazy load all passengers for each bus when the passengers are accessed during the conversion to DTOs.
There are two ways to disable lazy loading. Disabling LazyLoadingEnabled will re-activate lazy loading when it is enabled again. Disabling ProxyCreationEnabled will create entities that aren't capable of lazy loading themselves, so they won't start lazy loading after ProxyCreationEnabled is enabled again. This may be the best choice when the context lives longer than just this single query.
But... many-to-many
As said, this work-around relies on relationship fixup. However, as explained here by Slauma, relationship fixup doesn't work with many-to-many associations. If Bus-Passenger is many-to-many, the only thing you can do is fix it yourself:
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 the whole thing becomes even less appealing.
Third-party tools
There is a library, EntityFramework.DynamicFilters that makes this a lot easier. It allows you to define global filters for entities, that will subsequently be applied any time the entity is queried. In your case this could look like:
modelBuilder.Filter("Awake", (Person p) => p.Awake, true);
Now if you do...
Context.Busses.Where(b => b.IsDriving)
.Include(b => b.People)
...you'll see that the filter is applied to the included collection.
You can also enable/disable filters, so you have control over when they are applied. I think this is a very neat library.
There is a similar library from the maker of AutoMapper: EntityFramework.Filters
Entity Framework core work-around
Since version 2.0.0, EF-core has global query filters. These can be used to set predefined filter on entities that are to be included. Of course that doesn't offer the same flexibility as filtering Include on the fly.
Although global query filters are a great feature, so far the limitation is that a filter can't contain references to navigation properties, only to the root entity of a query. Hopefully in later version these filters will attain wider usage.
Now EF Core 5.0's Filter Include method now supports filtering of the entities included
var busses = _Context.Busses
.Include(b => b.Passengers
.Where(p => p.Awake))
.Where(b => b.IsDriving);
Disclaimer: I'm the owner of the project Entity Framework Plus
EF+ Query IncludeFilter feature allows filtering related entities.
var buses = Context.Busses
.Where(b => b.IsDriving)
.IncludeFilter(x => x.Passengers.Where(p => p.Awake))
.ToList();
Wiki: EF+ Query IncludeFilter
In my case the Include was an ICollection, and also did not want to return them, I just needed to get the main entities but filtered by the referenced entity. (in other words, Included entity), what I ended up doing is this. This will return list of Initiatives but filtered by InitiativeYears
return await _context.Initiatives
.Where(x => x.InitiativeYears
.Any(y => y.Year == 2020 && y.InitiativeId == x.Id))
.ToListAsync();
Here the Initiatives and the InitiativeYears has following relationship.
public class Initiative
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<InitiativeYear> InitiativeYears { get; set; }
}
public class InitiativeYear
{
public int Year { get; set; }
public int InitiativeId { get; set; }
public Initiative Initiative { get; set; }
}