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

Solution 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

Solution 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);

Solution 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.

Solution 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);

View more solutions

Comments

[Jason Jong]

Code didnt come out properly: 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 your implementation 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;