title: Search based on a set of keywords
body: <p>I need to make a search based on a set of keywords, that return all the Ads related with those keywords. Then the result is a list of Categories with the Ads Count for each Category. </p>
  
  <p>The search is made in a KeywordSearch Table:</p>
  
  <pre><code>public class KeywordSearch
  {
      public int Id { get; set; }
      public string Name { get; set; }
      public Keyword Keyword { get; set; }
  }
  </code></pre>
  
  <p>Where the Keyword Table is:</p>
  
  <pre><code>public class Keyword
  {
      public int Id { get; set; }
      public string Name { get; set; }
  }
  </code></pre>
  
  <p>The Ads are related with the Keywords using the following Table:</p>
  
  <pre><code>public class KeywordAdCategory
  {
      [Key]
      [Column("Keyword_Id", Order = 0)]
      public int Keyword_Id { get; set; }
  
      [Key]
      [Column("Ad_Id", Order = 1)]
      public int Ad_Id { get; set; }
  
      [Key]
      [Column("Category_Id", Order = 2)]
      public int Category_Id { get; set; }
  }
  </code></pre>
  
  <p>Finally, the Category table:</p>
  
  <pre><code>public class Category
  {
      public int Id { get; set; }
      public string Name { get; set; }
  }
  </code></pre>
  
  <p>Example:</p>
  
  <ul>
  <li>Keywords: "Mercedes-Benz" and "GLK"</li>
  <li>KeywordSearch: "Mercedes" and "Benz" for the Keyword "Mercedes-Benz"
                "GLK" for the Keyword "GLK" </li>
  <li>Category: "Cars" and "Trucks"</li>
  <li><p>Ads: Car - Mercedes-Benz GLK
      Truck - Mercedes-Benz Citan</p>
  
  <p>If I search "Mercedes-Benz" I get:</p>
  
  <ul>
  <li>Cars: 1</li>
  <li>Trucks: 1</li>
  </ul>
  
  <p>If I search "Mercedes-Benz GLK" I get:</p>
  
  <ul>
  <li>Cars: 1</li>
  </ul>
  
  <p>If I search "Mercedes Citan" I get:</p>
  
  <ul>
  <li>Trucks: 1</li>
  </ul></li>
  </ul>
  
  <p>What I get until now:</p>
  
  <pre><code>var keywordIds = from k in keywordSearchQuery
                      where splitKeywords.Contains(k.Name)
                      select k.Keyword.Id;
  
  var matchingKac = from kac in keywordAdCategoryQuery
                      where keywordIds.Distinct().Contains(kac.Keyword_Id)
                      select kac;
  
  var addIDs = from kac in matchingKac
               group kac by kac.Ad_Id into d
               where d.Count() == splitKeywords.Count()
               select d.Key;
  
  var groupedKac = from kac in keywordAdCategoryQuery
                      where addIDs.Contains(kac.Ad_Id)               &lt;--- EDIT2
                      group kac by new { kac.Category_Id, kac.Ad_Id };
  
  var result = from grp in groupedKac
                  group grp by grp.Key.Category_Id into final
                  join c in categoryQuery on final.Key equals c.Id
                  select new CategoryGetAllBySearchDto
                  {
                      Id = final.Key,
                      Name = c.Name,
                      ListController = c.ListController,
                      ListAction = c.ListAction,
                      SearchCount = final.Count()
                  };
  </code></pre>
  
  <p>The problem is that I can't get only the Ads that match all Keywords.</p>
  
  <p>EDIT: </p>
  
  <p>When a keyword is made of 2 or more KeywordSearches like "Mercedes-Benz", the line "where d.Count() == splitKeywords.Count()" fails, because d.count = 1 and splitkeywords.Count = 2 for "Mercedes-Benz"</p>
  
  <p>Any Help?</p>