title: Linq distinct record containing keywords
body: <p>I need to return a distinct list of records based on a car keywords search like: "Alfa 147"</p>
  
  <p>The problem is that, as I have 3 "Alfa" cars, it returns 1 + 3 records (it seems 1 for the Alfa and 147 result, and 3 for the Alfa result)</p>
  
  <p>EDIT:</p>
  
  <p>The SQL-Server Query look something like this:</p>
  
  <pre><code>SELECT DISTINCT c.Id, c.Name /*, COUNT(Number of Ads in the KeywordAdCategories table with those 2 keywords) */
  FROM Categories AS c
  INNER JOIN KeywordAdCategories AS kac ON kac.Category_Id = c.Id
  INNER JOIN KeywordAdCategories AS kac1 ON kac.Ad_Id = kac1.Ad_Id AND kac1.Keyword_Id = (SELECT Id FROM Keywords WHERE Name = 'ALFA')
  INNER JOIN KeywordAdCategories AS kac2 ON kac1.Ad_Id = kac2.Ad_Id AND kac2.Keyword_Id = (SELECT Id FROM Keywords WHERE Name = '147')
  </code></pre>
  
  <p>My LINQ query is:</p>
  
  <pre><code>       var query = from k in keywordQuery where splitKeywords.Contains(k.Name) 
                      join kac in keywordAdCategoryQuery on k.Id equals kac.Keyword_Id
                      join c in categoryQuery on kac.Category_Id equals c.Id
                      join a in adQuery on kac.Ad_Id equals a.Id
                      select new CategoryListByKeywordsDetailDto
                      {
                          Id = c.Id,
                          Name = c.Name,
                          SearchCount = keywordAdCategoryQuery.Where(s =&gt; s.Category_Id == c.Id).Where(s =&gt; s.Keyword_Id == k.Id).Distinct().Count(),
                          ListController = c.ListController,
                          ListAction = c.ListAction
                      };
  
          var searchResults = new CategoryListByBeywordsListDto();
  
          searchResults.CategoryListByKeywordsDetails = query.Distinct().ToList();
  </code></pre>
  
  <p>The entities are:</p>
  
  <pre><code>public class Keyword
  {
      // Primary properties
      public int Id { get; set; }
      public string Name { get; set; }
  }
  // Keyword Sample Data:
  // 1356 ALFA
  // 1357 ROMEO
  // 1358 145
  // 1373 147
  
  public class Category
  {
      // Primary properties
      public int Id { get; set; }
      public string Name { get; set; }
  }
  // Category Sample Data
  // 1    NULL    1   Carros
  // 2    NULL    1   Motos
  // 3    NULL    2   Oficinas
  // 4    NULL    2   Stands
  // 5    NULL    1   Comerciais
  // 8    NULL    1   Barcos
  // 9    NULL    1   Máquinas
  // 10   NULL    1   Caravanas e Autocaravanas
  // 11   NULL    1   Peças e Acessórios
  // 12   1   1   Citadino
  // 13   1   1   Utilitário
  // 14   1   1   Monovolume
  
  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; }
  }
  // KeywordAdCategory Sample Data
  // 1356 1017    1
  // 1356 1018    1
  // 1356 1019    1
  // 1357 1017    1
  // 1357 1018    1
  // 1357 1019    1
  // 1358 1017    1
  // 1373 1019    1
  
   public class Ad
  {
      // Primary properties
      public int Id { get; set; }
      public string Title { get; set; }
      public string TitleStandard { get; set; }
      public string Version { get; set; }
      public int Year { get; set; }
      public decimal Price { get; set; }
  
      // Navigation properties
      public Member Member { get; set; }
      public Category Category { get; set; }
      public IList&lt;Feature&gt; Features { get; set; }
      public IList&lt;Picture&gt; Pictures { get; set; }
      public IList&lt;Operation&gt; Operations { get; set; }
  }
  
  public class AdCar : Ad
  {
      public int Kms { get; set; }
      public Make Make { get; set; }
      public Model Model { get; set; }
      public Fuel Fuel { get; set; }
      public Color Color { get; set; }
  }
  // AdCar Sample Data
  // 1017 Alfa Romeo 145 1.6TDI 2013  ALFA ROMEO 145 1.6TDI 2013  12  2       1.6TDI  1000    1   2013    1   20000,0000  2052    AdCar
  // 1018 Alfa Romeo 146 1.6TDI 2013  ALFA ROMEO 146 1.6TDI 2013  12  2   5   1.6TDI  1000    2   2013    1   20000,0000  2052    AdCar
  // 1019 Alfa Romeo 147 1.6TDI 2013  ALFA ROMEO  147 1.6TDI 2013 12  2   6   1.6TDI  1000    3   2013    1   20000,0000  2052    AdCar
  </code></pre>
  
  <p>The result I expect for the search of "ALFA" is "Cars: 3" and for the search of "ALFA 147" is "Cars: 1" and actually the result I get is "Cars: 1 \n Cars: 3"</p>