NullReferenceException when querying Ids using LINQ and Contains

Question

I am getting a NullReferenceException when trying to run the following query:

 var questions = db.questions
                .Where(t => Ids.Contains(t.id));


  foreach (var q in questions) // exception
  {
   var a = q.id;
  }

The exception rises in the foreach statement.

In the database I have a questions table with one record with an Id = 1.

If I query with t => t.id == 1 I get results.

The Ids is a List<long> type. When running the code, the Ids variable contains one item, which is the value 1.

My goal is to retrieve the questions with id (primary key) that equals to one or more Ids in the Ids list.

So for example: if the list contains the numebrs 1,3,5, the query should return the rows from the questions table that match records with the corresponding Ids,which are 1,3 and 5.

Using Entity Framework 6 with MySQL.

What happens when you call .ToList() after here: .Where(t => Ids.Contains(t.id));, like .Where(t => Ids.Contains(t.id)).ToList(); — Dawood Awan, Mar 01 '15 at 13:07
Either your `db` or `questions` variable is null. Did you debug your code? — Yuval Itzchakov, Mar 01 '15 at 13:07
@GrantWinney When I use toList I still get the same exception. I am debugging the code. — Liron Harel, Mar 01 '15 at 13:10
@GrantWinney I am not ef expert but is it possible to query like that if yes how ef would materialise the query with IDs having in memory collection? — Jenish Rabadiya, Mar 01 '15 at 13:12
@YuvalItzchakov Hi, db is not null as it being initialized and the query works when I use t => t.id == 1. I assume that questions is null because there is something wrong with the query, — Liron Harel, Mar 01 '15 at 13:12
@GrantWinney I've seen this code in StackOverflow: http://stackoverflow.com/questions/15879771/linq-filter-liststring-where-it-contains-a-string-value-from-another-liststri so I assume that hsould have worked out. — Liron Harel, Mar 01 '15 at 13:14
@GrantWinney I agree but will it also populate all the element of the collection within in clause of materialized query? — Jenish Rabadiya, Mar 01 '15 at 13:16
@Dirk I read it and I didn't find the answer to 'contains' there. — Liron Harel, Mar 01 '15 at 13:27
@IdanShechter try `db.questions.ToList().Where(t => Ids.Contains(t.id))` just for testing. I think then error would disappear. — Jenish Rabadiya, Mar 01 '15 at 13:29
@IdanShechter almost all possibilities of a NullReferenceException are listed in that post. Let's see what could possibly be null in your code: db, questions, Ids and t. — Dirk, Mar 01 '15 at 13:29
@JenishRabadiya you code db.questions.ToList().Where(t => Ids.Contains(t.id)) worked! Smart solution. Write an answer and I will accept it. Thanks — Liron Harel, Mar 01 '15 at 13:32
@IdanShechter I know it would work but it will apply filter after getting records from db instead we should find out some way to filter it in sql itself. Why to fetch all the records when you need only few? — Jenish Rabadiya, Mar 01 '15 at 13:34
@JenishRabadiya this workaround worked for me: https://social.msdn.microsoft.com/Forums/en-US/095745fe-dcf0-4142-b684-b7e4a1ab59f0/where-in-clause?forum=adodotnetentityframework — Liron Harel, Mar 01 '15 at 14:24
@IdanShechter so did you tried that expression builder solution? You should post answer here if you got solution it may be helpful to others — Jenish Rabadiya, Mar 01 '15 at 14:43

score 1 · Answer 1 · answered Mar 01 '15 at 14:46

I found a workaround.

  static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
    {
        if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
        if (null == values) { throw new ArgumentNullException("values"); }
        ParameterExpression p = valueSelector.Parameters.Single();
        // p => valueSelector(p) == values[0] || valueSelector(p) == ...
        if (!values.Any())
        {
            return e => false;
        }
        var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));
        var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));
        return Expression.Lambda<Func<TElement, bool>>(body, p);
    }

And than use:

  var questions = db.questions.Where(
                    BuildContainsExpression<question, long>(e => e.id, Ids));

The solution I've got from here

score 0 · Answer 2 · answered Mar 01 '15 at 13:48

If this is your Ids

 List<long> Ids = new List<long> {1, 3, 5};

and this is a sample Question list

var questions = new List<Questions>()
                        {
                            new Questions {Id = 1},
                            new Questions {Id = 3},
                            new Questions {Id = 5},
                            new Questions {Id = 4}
                        }

this line should work fine.

var result = questions.Where(e => Ids.Contains(e.Id)).ToList();

NullReferenceException when querying Ids using LINQ and Contains

2 Answers2