Unable to create a null constant value of type 'System.Int32[]'.

Question

By using the following

  PagedData.Products = from p in db.Products
                                     where (from m in p.Manufacturers
                                            where model.man.Contains(m.ManufacturerID)
                                            select m).Any()
                                     where (from s in p.Sizes
                                            where model.size.Contains(s.SizeID)
                                            select s).Any()
                                     where (from c in p.Colors
                                            where model.color.Contains(c.ColorID)
                                            select c).Any()
                                     select p;

i get this error

Unable to create a null constant value of type 'System.Int32[]'. Only entity types, enumeration types or primitive types are supported in this context.

I got the point of the error, but i cannot figure out how should i fix it. The model.man model.size and model.color are arrays of integer, that may be also null.

have you tried to check null values like `where model.size!=null && model.size.Contains(s.SizeID)` — esiprogrammer, Mar 25 '16 at 09:26
The arrays may be null or the integers inside might be null? If the former is the case, just use a List instead and make it default empty — Alexander Derck, Mar 25 '16 at 09:26
What is the use cae here? None of the arrays may be null. Known limitation. Is one is null / emty, then do not put the where clause into the select statement. — TomTom, Mar 25 '16 at 09:30
Use model.size = (model.size ?? new int[0]) before executing the query. I wonder why model.size = null is a relevant case in your software. — Jeroen Heier, Mar 25 '16 at 09:30
what is 'model' variable you are using? Where is it declared? — Sasha, Mar 25 '16 at 09:31
try to split up your query to find out where is exactly the exception — esiprogrammer, Mar 25 '16 at 09:37

score 6 · Accepted Answer · answered Mar 25 '16 at 09:33

6

Since all conditions must be true to pass any Product you should first check if all arrays have any content at all:

if (model.man != null && model.size != null && model.color != null
      && model.man.Any() && model.size.Any() && model.color.Any())
{
    PagedData.Products = from p in db.Products ...

Now you won't execute a query if you know upfront that it doesn't return any data anyway. And it will not throw the exception because you never run the query with null arrays.

answered Mar 25 '16 at 09:33

Gert Arnold

105,341
31
202
291

It wasn't me! :) Actually not all conditions must be true, i wish there was something like || between the where clauses. – OrElse Mar 25 '16 at 09:46
1

That's a different issue, but there's a common way to do this: http://stackoverflow.com/a/14622200/861716 – Gert Arnold Mar 25 '16 at 09:47
Except that you do not need some external library for this - you assign the query to an IQueryable variable and add the conditions to this step by step. – TomTom Mar 25 '16 at 09:55
2

@TomTom That's only useful for chaining where clauses (`And`), with Linqkit you can chain expressions as `Or`. – Alexander Derck Mar 25 '16 at 10:18

Ivan Stoev · Answer 2 · 2016-03-25T10:24:32.450

I would prefer dynamically building the Where clause using method syntax and ifs, but if you wish to embed the conditions inside the query, you need to ensure that IEnumerables that you use for Contains criteria are not null. And that should happen outside the query:

var man = model.man ?? Enumerable.Empty<int>();
var size = model.size ?? Enumerable.Empty<int>();
var color = model.color ?? Enumerable.Empty<int>();

PagedData.Products = from p in db.Products
                                     where (from m in p.Manufacturers
                                            where man.Any() && man.Contains(m.ManufacturerID)
                                            select m).Any()
                                     where (from s in p.Sizes
                                            where size.Any() && size.Contains(s.SizeID)
                                            select s).Any()
                                     where (from c in p.Colors
                                            where color.Any() && color.Contains(c.ColorID)
                                            select c).Any()
                                     select p;

Note that filter.Any() && filter.Contains(...) make no sense and is equivalent to filter.Contans(...). If you want to ignore the empty filter, then you should use !filter.Any() || filter.Contans(...).

So IMO your query should be either like this

var man = model.man ?? Enumerable.Empty<int>();
var size = model.size ?? Enumerable.Empty<int>();
var color = model.color ?? Enumerable.Empty<int>();

PagedData.Products = from p in db.Products
                     where (from m in p.Manufacturers
                            where !man.Any() || man.Contains(m.ManufacturerID)
                            select m).Any()
                     where (from s in p.Sizes
                            where !size.Any() || size.Contains(s.SizeID)
                            select s).Any()
                     where (from c in p.Colors
                            where !color.Any() || color.Contains(c.ColorID)
                            select c).Any()
                     select p;

or this

var query = db.Products.AsQueryable();
if (model.man != null && model.man.Length > 0)
    query = query.Where(p => p.Manufacturers.Any(m => model.man.Contains(m.ManufacturerID)));
if (model.size != null && model.size.Length > 0)
    query = query.Where(p => p.Sizes.Any(s => model.size.Contains(s.SizeID)));
if (model.color != null && model.color.Length > 0)
    query = query.Where(p => p.Colors.Any(c => model.color.Contains(c.ColorID)));
PagedData.Products = query;

I never like to include such things as `man.Any()` in a LINQ statement that's translated into SQL. Did you check the generated SQL? The local sequence `man` must be converted to a table structure, which is done by a chain of `UNION`-d single-row selects. This can really hit performance when the arrays are "large". If the conditions are additional, of course your last code fragment is the way to go, although you should add a condition causing to return nothing if none of the arrays have content. — Gert Arnold, Mar 25 '16 at 10:46
@GertArnold I don't like it either, and prefer the other way as stated at the beginning. Other than that, I agree that your answer is better if OP really wants to return empty result set as in the original query, but my point is that probably he wants to ignore the filters when `null` or `empty`, and return a full set in such case. Of course that's my assumption, but sounds logical to me:) — Ivan Stoev, Mar 25 '16 at 11:04
@GertArnold Shouldn't local sequence `man` (which is list of `int`) be converted to constant `IN (....)`? — Ivan Stoev, Mar 25 '16 at 11:08
Yes, they are, but on top of that they're also converted into this "SQL table" to satisfy the `Any`. There's a lot of redundancy going on there. — Gert Arnold, Mar 25 '16 at 11:10
By the way, I think in the mean time the OP has concluded that he [prefers chained `or` clauses](http://stackoverflow.com/questions/36217216/unable-to-create-a-null-constant-value-of-type-system-int32/36217753?noredirect=1#comment60066850_36217370) by using a predicate builder. Whether or not they want all or nothing with empty arrays I don't know. In my own code I have both cases as business cases. — Gert Arnold, Mar 25 '16 at 11:14
@GertArnold Sure. What about the previous discussion, I checked the generated SQL and see what you mean. I'm always surprised they do optimizations like constant `IN` list and don't do simple optimizations like evaluating a simple constant predicates :) — Ivan Stoev, Mar 25 '16 at 11:26
Sigh... yes. At least they recently turned it into more robust query. In previous versions the `UNION`s were nested, causing a "too deep nesting" SQL error with only 45-50 elements in the array. — Gert Arnold, Mar 25 '16 at 11:29

Unable to create a null constant value of type 'System.Int32[]'.

2 Answers2