How to get ObjectResult from Entity Framework using a list of Identities

Question

I have a HashSet of Identity values that I need to use as the query values to return a ObjectResult from the Entity Framework

Here's the HashSet:

HashSet<int> officeIds = new HashSet<int>();

Here's the query that I'm trying to run more or less:

ObjectResult<FilingOffice> offices = ctx.FilingOffice.Where(office => office IN officeIds.ToList());

The "office => office IN officeIds.ToList()" part of the above is what I can't get to work and haven't found any samples on the web for returing objects given a list of primary keys.

ctx is the System.Data.Objects.ObjectContext

Answer 1

The examples others have given won't work in the Entity Framework today, because you can't mix client and serverside enumerations in LINQ 2 Entities.

Instead you need to build an OR expression, manually.

I run a series of EF Tips and this tip shows you how to build an OR expression up.

Hope this helps

Alex

Answer 2

I have had similar issues a lot of times, another Stack Overflow question with good information is: Most efficient way to get multiple entities by primary key?

I prefer to use:

var entities = db.Entities.WhereIn(x => x.Id, ids);

Answer 3

Try the following.

var offices = ctx.FilingOffice.Where(o => officeIds.ToList().Contains(o.Id));

But I am not absolutly sure if the Entity Framework supports this query - I tend to believe that you will have to store officeIds.ToList() in a local variable.

Answer 4

There is an alternative way to work around the LINQ to Entities limitation. You can use Entity SQL supporting the IN clause.

string entitySql = String.Format("SELECT VALUE O FROM FilingOffice AS O WHERE O.Id IN {{{0}}}", String.Join(",", officeIds.ToList().ConvertAll(officeId => officeId.ToString()).ToArray()));
ObjectQuery offices = new ObjectQuery(entitySql, ctx);

Answer 5

I had similar issue which I resolved via an inner join. See my function below.

public IEnumerable<AccountsCache> GetAccountsById(IEnumerable<int> accountIds)
{
    var query =
        from regAccount in registeredAccounts
        join Ids in accountIds on regAccount.AccountId equals Ids
        select regAccount;
    return query;
}

And in your cirsumstances

HashSet<int> officeIds = new HashSet<int>();

ObjectResult<FilingOffice> offices = 
    from f in ctx.FilingOffice
    join Ids in officeIds on f.officeId equals Ids
select f;

Answer 6

I don't have any means for checking this currently, but it looks like you are trying seeing if the office object itself is in the list, you probably want to check if its ID is in the list of ID's you have, like

ObjectResult<FilingOffice> offices = ctx.FilingOffice.Where(office => office.Id IN officeIds.ToList());

If that doesn't work some indication of what happens when you run your code would be helpful.