Where In clause against nullable field in Linq

Question

How can I write Linq to Entities to make a where in clause similar to one in SQL Server? The issue is that the field I need to search against is nullable int.

I have following code, but it returns "Unable to create a constant value of type 'System.Collections.Generic.List`1'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

public IList<WantedInfoView> GetWanted(string idList)
{
    List<int> contactIDList = new List<int>();
    contactIDList = idList.Split(',').Select(n => int.Parse(n)).ToList();

    IEnumerable<WantedInfoView> wantedList = (
       from w in db.Wanteds
       where contactIDList.Contains(w.contact_id)
       select new WantedInfoView
       {
           ...
       }
    );
}

Which line is getting the error? – Abe Miessler Aug 09 '12 at 23:02 — Abe Miessler, Aug 09 '12 at 23:02
you might have to do `w.contact_id.Value` – Brad Rem Aug 09 '12 at 23:19 — Brad Rem, Aug 09 '12 at 23:19

score 1 · Accepted Answer · edited May 23 '17 at 12:12

See Collection-valued parameters with The Entity Framework?

The accepted answer links to Tip 8 - How to write 'WHERE IN' style queries using LINQ to Entities, which is what you are looking for.

WHERE IN clause thread seems to be tackling this very issue as well.

Long story short, you can't use collections as parameters in LINQ to Entities queries. The framework is unable to make an SQL query out of it. There are workarounds, although not very pretty.

Where In clause against nullable field in Linq

1 Answers1