Linq search text using 'and' operator

问题:

I have a one to many relationship in the following tables

Products [ProductId, Name(varchar)]

Keywords [KeywordId, Keyword(varchar)]

KeywordsToProducts[Id, ProductId, KeywordId]

Let's say that search text is "blue pro". I need to search for both keywords using operator 'and'. If I do the following:

string test="blue pro";
string[]words = test.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

var query = from p in Products 

join kp in KeywordsToProducts on p.ProductId equals kp.ProductId 

join kw in Keywords on kp.KeywordId equals kw.KeywordId 

where (words.All(x=>kw.Keyword.Contains(x)))

then I don't geet anything because field keyword contains only one word.

How can I join the 'Keywrods.Keyword' db field records in order to search using 'and' operator?

回答1:

If you join each matching KeywordsToProducts to Keywords you can gather them up and compare them:

var query = from p in Products
            join kp in KeywordsToProducts on p.ProductId equals kp.ProductId into kpj
            let kws = (from kp in kpj join kw in Keywords on kp.KeywordId equals kw.KeywordId select kw.Keyword)
            where words.All(w => kws.Any(kw => kw.Contains(w)))
            select p;

However, I think it is easier to understand (and possibly more efficient) if you do the KeywordsToProducts to Keywords join separately and first (note that it shows the join table would have been better named KeywordIdsToProductIds):

var kwToProducts = from kp in KeywordsToProducts
                   join kw in Keywords on kp.KeywordId equals kw.KeywordId
                   select new { kp.ProductId, kw.Keyword };

var query = from p in Products
            join kwp in kwToProducts on p.ProductId equals kwp.ProductId into kwpj
            where words.All(w => kwpj.Any(kwp => kwp.Keyword.Contains(w)))
            select p;

While I'm not (necessarily) a fan of mentioning it, also note EF navigation properties can hide the join table and make this query easier as well.

It would be something like:

var query = from p in Products
            where words.All(w => p.Keywords.Any(k => k.Contains(w)))
            select p;

I am assuming you meant to use String.Contains so users can type in parts of keywords and still find matches. If you want to require all of the keyword to be matched, here is the same code:

var query = from p in Products
            join kp in KeywordsToProducts on p.ProductId equals kp.ProductId into kpj
            let kws = (from kp in kpj join kw in Keywords on kp.KeywordId equals kw.KeywordId select kw.Keyword)
            where words.All(w => kws.Contains(w))
            select p;

Splitting subjoin:

var kwToProducts = from kp in KeywordsToProducts
                   join kw in Keywords on kp.KeywordId equals kw.KeywordId
                   select new { kp.ProductId, kw.Keyword };

var query = from p in Products
            join kwp in kwToProducts on p.ProductId equals kwp.ProductId into kwpj
            where words.All(w => kwpj.Select(kwp => kwp.Keyword).Contains(w))
            select p;

EF Navigation Property:

var query = from p in Products
            where words.All(w => p.Keywords.Contains(w))
            select p;