Question
I try to join a IQueryable<int>, which is a list of entity ids with an IQueryable<Entity>.
For this I use the following code:
IQueryable<Entity> entityQuery = _context.Entities.Where(x => ...);
IQueryable<int> idQuery = _context.AccessRights.Where(x => ...).Select(x => x.Id);
query = entityQuery.Join(idQuery, x => x.Id, x => x, (x, y) => x);
This code is working with in-memory lists, however if I try it with LINQ to sql, the Join is getting ignored.
I don't want to load the ids into memory. Is this even possible with LINQ to SQL?
Popular Answer
You could do something like:
IQueryable<Entity> entityQuery = _context.Entities.Where(x => ...);
IQueryable<int> idQuery = _context.AccessRights.Where(x => ...).Select(x => x.Id);
entityQuery = entityQuery.Where(x => idQuery.Contains(x.Id));
This will get the result you're looking for without loading the ids into memory. But it may convert it into a giant SQL IN statement, which wouldn't be ideal.
Or you can join to AccessRights directly:
IQueryable<Entity> entityQuery = from e in _context.Entities.Where(x => ...)
join ar in _context.AccessRights.Where(x => ...)
on e.Id equals ar.Id
select e;
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow