"Unable to create a constant value of type." How to avoid this error in LINQ query?

Question

I have this method:

public virtual IEnumerable<Invoice> GetHomePageInvoices(IList<Area> areas, FinancialYearLookup financialYear)
{
    var homePageInvoices = _db.Invoices.Where(x => areas.Any(z => z.Id == 3)).ToList();

    ...
}

Basically I'm trying to find any invoices where the area matches with any of those in the parameter area.

I'm getting the error:

Unable to create a constant value of type 'Models.Area'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Can anyone explain why this is happening and how to fix?

What LINQ provider are you using? LINQ to SQL? LINQ to Entities? Something else? — svick, Feb 22 '12 at 23:37

score 5 · Accepted Answer · answered Feb 22 '12 at 23:41

You cannot use an IList<Area> in the context of your Linq Provider (presumably Linq to Entities) - just extract the id's beforehand and use a Contains query which does work on a collection of primitives:

List<int> ids = areas.Select( x=> x.Id).ToList();
var homePageInvoices = _db.Invoices
                          .Where(x => ids.Contains(x.Id))
                          .ToList();

Also I assume you did not want to compare with a fixed value of 3 - so I changed your query accordingly - provided the Invoice entity has an Id property.

score 0 · Answer 2 · edited May 23 '17 at 12:11

a. It looks like you have a typo here:

z => z.Id == 3

but the main problem is b. I'm guessing you're using Linq to Entities but its not clear. In any case what is happening is that the query builder is trying to turn that 'areas.Any(...)' into SQL and it can't do it because areas is not an IQueryable from your database, but a local variable. I suggest you use something like this WhereIn custom Linq operator as described here or here. That will build a SQL in clause containing all the items in areas that you might want to match against.

"Unable to create a constant value of type." How to avoid this error in LINQ query?

2 Answers2