Entity Framework 4 – Where Entity.Id in Array

Here is a little trick if you want to issue a query to the database and select a batch of entities by ID only:

//assemble an array of ID values
int[] customerIds= new int[] { 1, 2, 3 };

var customers = from customer in context.CustomerSet
                where customerIds.Contains(customer.Id)
                select customer;

This will make Entity Framework issue an SQL query with the where clause “where customerId in (1,2,3)”, and thus, you can batch select specific entities with a single query.
I will get back to this idea in a later post because this is related to how I design my entities and aggregates in DDD.



  1. Frans Bouma says:

    Actually this is basic Linq query stuff, although it’s hard to do in a Linq provider (as the set contains works on can be a query as well as the parameter passed into contains ;))

    EF1.0 lacked this Contains operator, which has always surprised me.

  2. Steven says:

    It is pretty annoying that EF v1 lacks support for this. I agree that it’s basic LINQ stuff (no matter how complicated how complicated this is for a LINQ provider).

  3. Bert says:

    Any way to get this working in EF v1. How would you handle this?

  4. Schmalls says:

    The best workaround I have found for EF 1 is the following:

    //assemble an array of ID values
    int[] customerIds= new int[] { 1, 2, 3 };
    string customerIdsString = customerIds.ToString(", ");

    var customers =
    from customer in context.CustomerSet.Where("it.Id in {" + customerIdsString + "}")
    select customer;

    For this to work correctly you need to add some extension methods as described at codemeit. I found the initial where clause idea from an MSDN thread.

  5. Salem M'dimegh says:

    EF translate `customerIds.Contains(customer.Id)` to ` … WHERE (1 = id) AND (2 = id) AND (3 = id) AND … `. It’s realy bad with 10.000 items

  6. dhiren ppatel says:

    thanks its work…….

  7. Noman Sadiq says:

    Is this possible where can pass sql query in ef where ? like that
    var query = db.users.where(“username like ‘test user%’ “);

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s