none
Entity framework - Error when filtering a IQueryable by "Contains in list" or list is null

    Question

  • This is the code which I'm using

    var dta = _jobRepository.Jobs.Where(x => filter.CategoryIds == null || filter.CategoryIds.Contains(x.CategoryId));

    Jobs is returning IQueryable<Job>. I want to check if CategoryId is in list or return all if CategoryIds is null. I'm getting follong error on running this code

    Cannot compare elements of type 'System.Collections.Generic.List`1'. Only primitive types, enumeration types and entity types are supported.

    Please help.

    Saturday, November 09, 2013 4:53 PM

Answers

  • Try it like this.  You can't pass a List to a SQL query like that. 

    var dta = _jobRepository.Jobs;
    if ( filter.CategoryIds != null )
    {  
      dta = dta.Where(filter.CategoryIds.Contains(x.CategoryId));
    }
    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, November 10, 2013 3:51 PM

All replies

  • Hi Moncy K !

    The problem is that CategoryIds is nonullable value (It cannot takes null ) .

    Instead this :

    Where(x => filter.CategoryIds == null

    Try this :

    Where(x => (int?)filter.CategoryIds == null

    Cheers

    Saturday, November 09, 2013 8:14 PM
  • Hi.. thanks for the reply. I'm sorry.. my question was not exhaustive.

    CategoryIds is of type List<int> so it can be null.

    If i use _jobRepository.Jobs.ToList().Where(x => filter.CategoryIds == null || filter.CategoryIds.Contains(x.CategoryId));

    it works fine. I've issue only with where condition in iQueryable

    If i use _jobRepository.Jobs.ToList().Where(x => filter.CategoryIds == null); its working

    or

    if i use _jobRepository.Jobs.ToList().Where(x => filter.CategoryIds.Contains(x.CategoryId));

    All goes wrong when i use || operator with IQueryable output

    Sunday, November 10, 2013 3:37 PM
  • Try it like this.  You can't pass a List to a SQL query like that. 

    var dta = _jobRepository.Jobs;
    if ( filter.CategoryIds != null )
    {  
      dta = dta.Where(filter.CategoryIds.Contains(x.CategoryId));
    }
    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, November 10, 2013 3:51 PM
  • Thanks.. It worked.
    Monday, November 11, 2013 6:47 AM