C# LINQ Filter records in child tables-CodePudding

I have a main table "SALES" and two secondary tables "PRODUCTS" and "SERVICES", I need to select only the records in "SALES" that contain some product or service entered by the user, I don't need to bring the sales records and products, just filter. First I made the filter in the table "SALES" by date of sale:

var query = (from p in _contexto.sales
where p.datesale.Value.Date >= Convert.ToDateTime(strDtI).Date &&
p.datesale.Value.Date <= Convert.ToDateTime(strDtF).Date
select p);

Now let's say the user wants to filter also the sales that have products or services with the words in a string Array

words = ['apple', 'beef', 'cleaning', 'haircut']

if you receive the array of words, I tried the filter below, but it didn't work, it kept bringing all the records.

var queryi = (from i in _contexto.products
where words.Contains(i.name) || words.Contains(i.description) select i);
//var queryj = (from i in _contexto.services
//where words.Contains(i.name) || words.Contains(i.description) select i);

//query = query.Where(p => queryi.All(c => c.idsale != p.id) || queryj.All(c => c.idsale != p.id));
query = query.Where(p => queryi.All(c => c.idsale != p.id));

where am I failing, and is there a better and more performant way to do this? Thank you!

CodePudding user response：

Using more descriptive variable names, and assuming you meant to only find products that have the exact same name or description as one of the words, you would have:

var salesInPeriod = from s in _contexto.sales
                    where Convert.ToDateTime(strDtI).Date <= s.datesale.Value.Date &&
                          s.datesale.Value.Date <= Convert.ToDateTime(strDtF).Date
                    select s;

var matchingidsales = from p in _contexto.products
                      where words.Contains(p.name) || words.Contains(p.description)
                      select p.idsale;

var ans = from s in salesInPeriod
          where matchingidsales.Contains(s.id)
          select s;

PS: I inverted the date comparison since I think it makes it easier to see you are doing a between test.