Why can't I filter an IQueryable like I can with a List?

asp.net-mvc c# entity-framework-core iqueryable linq

Question

I'm refactoring a project a little bit and have come back to a problem that I never got solved in the past. I am trying to perform multiple filters on a query of an EF Core db.

In the past I had tried to setup a series of Where statements that did a check on if the filter statement was null OR by matching the filter.

This returned a nullReferenceException somewhere in the query. I resolved that issue by running my query without the filters and then applying the filters to my List afterwards.

I came back and created a WhereIf extension and hoped it would maybe fix my issues while also making the code a little cleaner, but the same issue pops up.

I currently have four filters I am trying to run on the query, and it passes the initial filter fine, but if any of the other three filters are chosen, the query has a nullReferenceException.

This again works if I get a list from a general query and the first filter, and then subsequently apply the filters to my list.

This is what I'd like to do:

IQueryable<Film> films = _context.Films
    .Include(f => f.Media)
    .Include(f=> f.Audio)
    .Include(f => f.FilmGenres)
        .ThenInclude(fg => fg.Genre)
    .WhereIf(!string.IsNullOrEmpty(vm.SearchValue), f => f.Name.ToLower().Contains(vm.SearchValue.ToLower()))
    .WhereIf(!string.IsNullOrEmpty(vm.MediaFilter), f => f.Media.Name == vm.MediaFilter)
    .WhereIf(!string.IsNullOrEmpty(vm.AudioFilter), f => f.Audio.Name == vm.AudioFilter)
    .WhereIf(!string.IsNullOrEmpty(vm.GenreFilter), f => f.FilmGenres.Any(fg => fg.Genre != null && fg.Genre.Name == vm.GenreFilter));

Here is the WhereIf method:

public static IQueryable<TSource> WhereIf<TSource>(this IQueryable<TSource> source, bool condition, Expression<Func<TSource, bool>> predicate)
        {
            // Performs a Where only when the condition is met

            if (condition)
            {
                source = source.Where(predicate);
                return source;
            }

            return source;
        }

The filter on vm.SearchValue goes through fine, and when I step through it, the value is a IQueryable as expected. Once it hits any of the other filters, then it comes back with the nullReferenceException (when it finally gets to the ToList() later on). If I look at the value of source prior to return, it shows it having the null exception in the Result View.

I have tried doing each line one by one (with a films = film.Where(...)). I have tried skipping the WhereIf and just doing if statements and a standard Where, and all of this has the same result.

It is only when I create a List object, populated by a general query of the data, and then filter that List object that I get it to work.

So, what is the problem with filtering on an IQueryable in EF Core? Is this not allowed, or am I doing something wrong?

Update: All of the Film objects do have Media/Audio/FilmGenre objects and everything has been Included. And I have verified that items in the IQueryable source has all of these items prior to the Where statement in the WhereIf method.

I have tried separating each filter statement out separately, and that includes skipping the WhereIf method and using if statements as well.

Additionally, only one filter can be selected at a time (for now). Those that aren't selected result in the condition being false and there is no problem. It only hiccups when working on an active filter. For example, I'll do an initial search that only checks the vm.SearchValue. That will give me a list of Film's and options to filter and sort. Then when I select to filter by Audio or Media, etc., I get the problem.

Here is the stack trace:

   at lambda_method(Closure , InternalEntityEntry )
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.SimpleNonNullableDependentKeyValueFactory`1.TryCreateFromCurrentValues(InternalEntityEntry entry, TKey& key)
   at Microsoft.EntityFrameworkCore.Query.Internal.WeakReferenceIdentityMap`1.CreateIncludeKeyComparer(INavigation navigation, InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.IncludeCore(Object entity, INavigation navigation)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Int32 currentNavigationIndex, Boolean queryStateManager)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Boolean queryStateManager)
   at Microsoft.EntityFrameworkCore.Query.Internal.GroupJoinInclude.GroupJoinIncludeContext.Include(Object entity)
   at Microsoft.EntityFrameworkCore.Query.Internal.GroupJoinInclude.GroupJoinIncludeContext.Include(Object entity)
   at Microsoft.EntityFrameworkCore.Query.Internal.GroupJoinInclude.GroupJoinIncludeContext.Include(Object entity)
   at Microsoft.EntityFrameworkCore.Query.Internal.GroupJoinInclude.GroupJoinIncludeContext.Include(Object entity)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_GroupJoin>d__26`4.MoveNext()
   at System.Linq.Enumerable.<SelectManyIterator>d__165`3.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source, Int32& length)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

Images Below:

  1. Here is source's Results View when going through the SearchValue filter prior to the Where statement in WhereIf
  2. Here it is after that Where statement
  3. Here it's going through the AudioFilter - predicate shown.
  4. Here is the source prior to the Where statement when doing the AudioFilter - same as after the SearchValue filtering
  5. And finally, after doing the Where when doing the Audio filtering

Here is source's Results View when going through the SearchValue filter prior to the Where statement in WhereIf

Here it is after that Where statement

Here it's going through the AudioFilter - predicate shown.

Here is the source prior to the Where statement when doing the AudioFilter - same as after the SearchValue filtering

And finally, after doing the Where when doing the Audio filtering

UPDATE: This has been resolved. There was another check involving the application user that was causing a client side evaluation, that was moved and now the query works as intended.

1
0
4/12/2019 4:37:35 PM

Popular Answer

This answer's off the cuff and is a bit of guesswork on my part, so I apologize if it's not helpful.

Anyway, a couple things are standing out to me.

First, your WhereIf() function - it's not quite doing what a Where() would do. Where() takes a source and returns a second source where the recordset is winnowed down. Notably, it doesn't change the original datasource at all. Well, your WhereIf() is trying to do that - it's changing the 'source' variable that's being passed in to the function. I did some googling, and the IQueryable doesn't look like it's immutable which means it can be changed without creating a new class instance, so I'm not positive that this line of code isn't screwing up the foundation its building off of:

source = source.Where(predicate);

... it would explain the results you're getting. The first 'WhereIf' with a true condition works, but the subsequent one doesn't - because the first one messed with the base object it was working off of. At the very least, you should change it to 'return source.Where(predicate)', simply for code clarity (since your existing code makes it look like its trying to change it.)

Second, have you tried breaking the statement up? I mean, something like this:

var results = SomeLinq.SomeStatement(a => something(a))
        .Where(b => b == something)
        .Where(c => c == something)

... is the same thing as:

var mainQueryable = SomeLinq.SomeStatement(a => something(a));
var filtered = mainQueryable.Where(b => b == something);
var results = filtered.Where(c => c == something);

Which in turn would let you simplify the picture down for LINQ:

IQueryable<Film> films = _context.Films
    .Include(f => f.Media)
    .Include(f=> f.Audio)
    .Include(f => f.FilmGenres)
    .ThenInclude(fg => fg.Genre);
if (!string.IsNullOrEmpty(vm.SearchValue)) films = films.Where(f => f.Equals(vm.SearchValue, StringComparison.OrdinalIgnoreCase);
if (!string.IsNullOrEmpty(vm.MediaFilter)) films = films.Where(f => f.Media.Name == vm.MediaFilter);
// etc...

... so the final LINQ statement doesn't have superfluous WHERE clauses that don't actually filter anything down.

Anyway, hope these help out a bit.

0
4/11/2019 2:08:30 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