One possibility to redefine the query is this:
var query = from i
dbContext.Interviews.Include(r => r.InterviewRooms)
// add the other part of the Many-to-Many relation
// "join" both sides
where i.InterviewRooms.Contains(ir) &&
// filter by the roomIds list
(roomIds.Contains(ir) || roomIds.Count == 0) &&
// filter by the dates list
(dates.Contains(.WhenCreated.Date) || dates.Count == 0)
orderby i.WhenCreated descending
Please note that I have not tested the exact query as I am lacking your data model.
Additionally you should know that we have introduced special handling of IEnumerable.Contains
calls in your case) that includes IN
clause or in case with high number of values in the lists a temporary table
You may want to explore the SQL that is generated in your scenario and compare its performance against hand-crafted SQL query. If you see significant performance differences I would suggest that you rewrite your query as a Stored Procedure or use our ADO API to execute the raw SQL to perform the same calculations.
If you are experiencing any issue with the suggested query, do not hesitate to contact us.
Check out the latest announcement
about Telerik Data Access vNext as a powerful framework
able to solve core development problems.