0

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:)

Krzysztof
  • 152
  • 1
  • 2
  • 15

0 Answers0