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?
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;