0

I need to build a query using linq to return the rows matching all words in an array:

Example array (splitKeywords):

{string[2]}
    [0]: "RENAULT"
    [1]: "CLIO"

KeywordSearch table:

public class KeywordSearch
{
    // Primary properties
    public int Id { get; set; }
    public string Name { get; set; }
    public Keyword Keyword { get; set; }
}

This table has the records:

Id: 1
Name: "RENAULT"
Keyword_Id: 3503

Id: 2
Name: "CLIO"
Keyword_Id: 3503

I want to get all the Keyword_Ids which match all the words in the array.

So far I have:

EDIT: 

var keywordSearchQuery = _keywordSearchRepository.Query;

var keywordIds = from k in keywordSearchQuery
                        where splitKeywords.All(word => word.Equals((k.Name)))
                        select k.Keyword.Id;

But It's not working. Any ideas?

Thanks

Improve this question
6
  • 1
    what is keywordSearchQuery and splitKeywords; and what does not working mean? Commented Jul 16, 2013 at 14:38
  • Do you mean you want to find the id for each element in the array? Commented Jul 16, 2013 at 14:38
  • Not sure exactly what you are after: Do you mean you want a list of items from your table which match ANY of the keywords in your array "splitKeywords" If this is the case your query should just be from k in keywordSearchQuery where splitKeywords.Contains(k.Name) Your current query using .All is never going to work, as you are specifying a condition that has to be true for all the strings in the keywordSearchQuery array Commented Jul 16, 2013 at 14:51
  • Hi, thanks. I want to get all the Keywords that match all the strings in the splitKeywords that is an array of strings built from a split(" "). The related words to search are in the KeywordSearches table, and I need to find all the Keywords that match. Commented Jul 16, 2013 at 15:39
  • ok. I think the answer given below by cadrell0 is correct then Commented Jul 16, 2013 at 15:49

2 Answers 2

Reset to default
3

First you need to combine all of the records with the same Keyword.Id into a single record. Do that with GroupBy.
Once you have them grouped, you can filter out the groups (KeywordIds), where all of the items (the individual records, the names), don't match at least one of the splitKeyWords.

Original - This checks that all Name values match a value in splitKeyWords

var results = keywordSearchQuery
                .GroupBy(k => k.Keyword_Id)
                .Where(g => g.All(k => splitKeyWords.Any(w => w.Equals(k.Name))))
                .Select(g => g.Key);

Updated - This checks that all values in splitKeyWords match a Name.

var results = keywordSearchQuery
                .GroupBy(k => k.Keyword_Id)
                .Where(g => splitKeyWords.All(w => g.Any(k => w.Equals(k.Name))))
                .Select(g => g.Key);

Note that if you have a Keyword.Id with names RENAULT, CLIO, and ASDF, it will match.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi thanks. I'm getting the Select part underlined with the title "bool does not contain a definition for Select", any idea?
I missed a closing param. There should be one more on the previous line. I'll update the answer
It's returning a Keyword that match RENAULT and CLIO but it's also returning a Keyword that match only RENAULT, any idea why is this happening? I had to change you GroupBy(k => k.Keyword_Id) by GroupBy(k => k.Keyword.Id)
I'd assume thats because your data looks like {Id: 1, Name: "RENAULT", Keyword_Id: 3503} {Id: 2, Name: "CLIO", Keyword_Id: 3503} {Id: 3, Name: "RENAULT", Keyword_Id: 4604}.... ie there is a keyword_id with just RENAULT, but not CLIO. Now the question is.. is the data correct, but the matching logic is wrong, or is the logic correct, but the data is wrong?
Each Keyword has all the KeywordSearches related to it. The keyword "Renault Clio" will have the KeywordSearch "RENAULT" and "CLIO", or if I have the Keyword "Renault Lisbon" I will have the keywordSearch "RENAULT" and "LISBON". I can have multiple "RENAULT" keywordSearch but all of them related with a diferent Keyword
|
0

This will go through your array and find the matching key for each element, if this is what you're looking for?

var ids splitKeywords.Select(k => keywordSearchQuery.Single(q => q.Name == k).Id).ToArray();

You'd have to do a bit more validation if the Name wasn't unique or if you weren't sure you'd get a match.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.