EF Core join entity IQueryable with id IQueryable

Question

EF Core join entity IQueryable with id IQueryable

c# entity-framework-core linq-to-sql

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?

Hello It's me

View more on Stack Overflow

AaronShockley · Accepted Answer · 11/9/2016 12:20:58 AM

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;

EF Core join entity IQueryable with id IQueryable

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related