Using Contains in Entity Framework for WHERE IN clause

Posted on March 16, 2013 by Michael Roma

A common problem with parameterized queries is when you have the need for a WHERE IN clause and the IN list is variable. This is solved with Entity Framework using the Contains methods of the list.

Consider you have the following domain:

// Base table entity
public abstract class TableBase
{
    [Key]
    public int id { get; set; }
}


// Customer entity
public class Customer: TableBase
{                    
    // customer fields   
    public string Name { get; set; }        
    public int? Type { get; set; }

    // contacts collection
    public virtual ICollection Contacts { get; set; }
}

// Contact entity
public class Contact : TableBase
{
    // contact fields
    public string FirstName { get; set; }
    public string LastName { get; set; }        
}

// Customer View entity (not mapped)
[NotMapped]
public class CustomerView
{
    // define customer
    public Customer Customer { get; set; }

    // define count of contacts
    public int ContactCount { get; set; }
}

You can use the following statement to get a filtered list of customers by types:

var typeList = new int[] { 2, 4, 5 };

var customerList = 
    Customers
        .Where(c => typeList.Contains(c.Type))
        .ToList();
comments powered by Disqus