Linq使用“和"运算符搜索文本 [英] Linq search text using 'and' operator

问题描述

我在下表中有一对多的关系

I have a one to many relationship in the following tables

产品 [ProductId，名称(varchar)]

Products [ProductId, Name(varchar)]

关键字 [KeywordId，关键字(varchar)]

Keywords [KeywordId, Keyword(varchar)]

KeywordsToProducts [Id，ProductId，KeywordId]

KeywordsToProducts[Id, ProductId, KeywordId]

让我们说搜索文本是"blue pro".我需要使用运算符和"来搜索这两个关键字. 如果我执行以下操作:

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

那么我什么都不做，因为field关键字仅包含一个单词.

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

如何使用'and'运算符来联接'Keywrods.Keyword'数据库字段记录?

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

推荐答案

如果将每个匹配的KeywordsToProducts加入Keywords，则可以将它们收集起来并进行比较:

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;

但是，我认为如果分别先进行KeywordsToProducts到Keywords join的操作，则更容易理解(可能更有效)(请注意，它表明联接表最好命名为):

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;

尽管我(不一定)不喜欢它，但还请注意EF导航属性可以隐藏联接表并使此查询也更容易.

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.

那会是这样的:

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

我假设您打算使用String.Contains，以便用户可以键入部分关键字并仍然找到匹配项.如果要要求所有关键字都匹配，请使用以下代码:

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;

拆分子联接:

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导航属性:

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

这篇关于Linq使用“和"运算符搜索文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！