1

The query:

List<int> companyIds = null;

(from car in context.GetTable<Car>()
where companyIds == null || companyIds.Contains(car.companyID)
select car)
.ToList();

The result:

at System.Linq.Enumerable.OfType[TResult](IEnumerable source) at System.Data.Linq.SqlClient.QueryConverter.VisitContains(Expression sequence, Expression value) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp) at System.Data.Linq.SqlClient.QueryConverter.VisitBinary(BinaryExpression b) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp) at System.Data.Linq.SqlClient.QueryConverter.VisitWhere(Expression sequence, LambdaExpression predicate) at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node) at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node) at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at EVaultSDK.Services.CompanyService.Get...

If I add ToList

 context.GetTable<Car>().ToList()

It works

vborutenko
  • 4,323
  • 5
  • 28
  • 48
  • Is the question mark operator applicable here? – Ctznkane525 Sep 14 '18 at 16:24
  • 1
    @maccettura Not sure I agree with that, the code presented above is logically correct, though it's missing a `select` so I'm not sure it's complete. – DavidG Sep 14 '18 at 16:27
  • @maccettura,read about short circuit,if the first clause is true,no need to perform the second clause – vborutenko Sep 14 '18 at 16:28
  • @cosset so you want every record where companyIds is null, and where the companyIds contains x? – maccettura Sep 14 '18 at 16:29
  • @DavidG,you are right,added the select – vborutenko Sep 14 '18 at 16:29
  • @maccettura,I want if companyIds is null - return all cars,if not - return only cars that have appropriate companyId.Of course I made companyIds = null before the query only for example – vborutenko Sep 14 '18 at 16:30
  • Context is not null,companyIds is null and linq try to execute contains on null,but it shouldn't execute the second clause as the first is true – vborutenko Sep 14 '18 at 16:39
  • 2
    @cosset can you try adding `ToList` here: `context.GetTable().ToList()` and see if you get the exception again. And can you post the full Stack trace of the exception ? – Selman Genç Sep 14 '18 at 16:44
  • yep, stack trace confirms my suspicion. – Selman Genç Sep 14 '18 at 16:59
  • 1
    @CetinBasoz,yes,your answer can be called solution,but it's logically incorrect,if I pass to method empty(not null) companyIds,I expect the method return nothing,for that puprpose companyIds can be nullable, – vborutenko Sep 14 '18 at 17:04
  • @cosset, OK I will edit my solution just for you. – Cetin Basoz Sep 14 '18 at 17:10
  • If you add context.GetTable().ToList() it doesn't really work. In that case you are beating your purpose and bringing in all the data regardless of what you have in your selections. With small data it would go unnoticeable but if you have large data beware you are pulling down the wire the whole table every time. – Cetin Basoz Sep 14 '18 at 17:22
  • @CetinBasoz,yes,I know about it – vborutenko Sep 14 '18 at 17:29

2 Answers2

3

I think the reason is you are using LINQ to SQL, your query needs to be translated into SQL and you get the exception while this translation happens. The Contains is translated into IN operator in SQL. But since the list is null I think the LINQ to SQL provider throws an exception.

This is the reason why you don't get the exception when you add ToList after GetTable<Car>(), this causes you to fetch and load all Cars in memory, therefore the query runs on memory so don't need to be translated to SQL and the short-circuiting works as expected.

Normally if you call Contains on a null list, you should get NullReferenceException but you are getting ArgumentNullException. So you should check stack trace and if it's the case, don't use a null list in your query.

Edit: The stack trace you posted confirms my assumption. Somewhere along the road OfType method is called on the list and it causes the exception.

Selman Genç
  • 100,147
  • 13
  • 119
  • 184
2

You already got a good answer on why you're getting the error - not everything in C# translates nicely to SQL, and you often don't find out until runtime.

Just in case a future visitor hits this question looking for a workaround, I'd suggest using the fact that you can build a query up and it won't retrieve any data until you call ToList() (or Single(), etc...):

List<int> companyIds = null;

var query = context.GetTable<Car>();
if (companyIds != null)
    query = query.Where(car => companyIds.Contains(car.companyID));

var result = query.ToList();
Grant Winney
  • 65,241
  • 13
  • 115
  • 165
  • Hey be prepared to be down voted for providing a solution instead of an answer to his question:) BTW you miss AsQueryable(). – Cetin Basoz Sep 14 '18 at 17:14