I'm trying to write a Linq query that will translate to SQL to get existing record based on provided lists.
searchRequest is a list of:
class SearchRequestDTO
{
public string Book { get; set; }
public string Page { get; set; }
public string DocumentNumber { get; set; }
}
This is my initial idea:
var documentNumbersFlat = new HashSet<string>(searchRequest.Select(d => d.DocumentNumber));
var books = new HashSet<string>(searchRequest.Select(d => d.Book));
var pages = new HashSet<string>(searchRequest.Select(d => d.Page));
var queryable = _dbSet.Include(i => i.Searches).Where(a =>
a.Searches.Count == searchRequest.Count &&
a.Searches.All(i => documentNumbersFlat.Contains(i.DocumentNumber) || (books.Contains(i.Book) && pages.Contains(i.Page))));
But I dont think it is correct because I want exact Book and Page and in this case Im checking two separate list, so ideally I wanted to have something like that:
a.Searches.All(i => searchRequest.Contains(new {i.DocumentNumber, i.Book, i.Page)));
Alternative (like a last resort):
var documentNumbersFlat = new HashSet<string>(searchRequest.Select(d => d.DocumentNumber));
var books = new HashSet<string>(searchRequest.Select(d => $"{d.Book}_{d.Page}"));
var queryable = _dbSet.Where(a =>
a.Searches.Count == searchRequest.Count &&
a.Searches.All(i => documentNumbersFlat.Contains(i.DocumentNumber) || books.Contains($"{i.Book}_{i.Page}")));
Additionally I want to point out I`m looking for best way of handling it in terms of performance.
All suggestions appreciated:)