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.

Answer 1

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;

Answer 2

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.