Linq distinct record containing keywords

Question

I need to return a distinct list of records based on a car keywords search like: "Alfa 147"

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)

EDIT:

The SQL-Server Query look something like this:

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

My LINQ query is:

       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 => s.Category_Id == c.Id).Where(s => s.Keyword_Id == k.Id).Distinct().Count(),
                        ListController = c.ListController,
                        ListAction = c.ListAction
                    };

        var searchResults = new CategoryListByBeywordsListDto();

        searchResults.CategoryListByKeywordsDetails = query.Distinct().ToList();

The entities are:

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<Feature> Features { get; set; }
    public IList<Picture> Pictures { get; set; }
    public IList<Operation> 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

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"

Answer 1

The kac is not filtering words... so this joins of kac, kac1 and kac2 will return 3 lines, cause this is the numbers of keywords for this ad

You should remove it..

Try this:

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 kac1 ON kac1.Keyword_Id = (SELECT Id 
                                                      FROM Keywords 
                                                      WHERE Name = 'ALFA')
                                AND kac1.Category_Id = c.Id
INNER JOIN 
    KeywordAdCategories AS kac2 ON kac1.Ad_Id = kac2.Ad_Id 
                                AND kac2.Keyword_Id = (SELECT Id 
                                                       FROM Keywords 
                                                       WHERE Name = '147')
                                AND kac2.Category_Id = c.Id

I did a test...

Setting the ambient as

    declare @Keywords table(id int,name varchar(max))
    insert into @Keywords(id,name)
    values (1356,'ALFA')
    ,(1357,'ROMEO')
    ,(1358,'145')
    ,(1373,'147')

    declare @Categories table(id int, name varchar(max))
    insert into @Categories(id,name)
    values (1,'Carros')
    ,(2,'Motos')


    declare @KeywordAdCategories table(Keyword_Id int, ad_Id int,Category_Id int)
    insert into @KeywordAdCategories (Keyword_Id , ad_Id,Category_Id)
    values (1356, 1017,1)
    ,(1356, 1018,1)
    ,(1356, 1019,1)
    ,(1357, 1017,1)
    ,(1357, 1018,1)
    ,(1357, 1019,1)
    ,(1358, 1017,1)
    ,(1373, 1019,1)

I run these two queries:

--query 1
SELECT 
    c.Id, c.Name,COUNT(*) as [count]
FROM 
    @Categories AS c
INNER JOIN 
    @KeywordAdCategories AS kac1 ON kac1.Keyword_Id = (SELECT Id 
                                                       FROM @Keywords 
                                                       WHERE Name = 'ALFA')
                                 AND kac1.Category_Id = c.Id
GROUP BY 
    c.Id, c.Name

I get this result set:

  Id          Name       count
  ----------- ---------- -----------
  1           Carros     3

and the second query for two words...

--query 2
SELECT 
    c.Id, c.Name,COUNT(*) as [count]
FROM 
    @Categories AS c
INNER JOIN 
    @KeywordAdCategories AS kac1 ON kac1.Keyword_Id = (SELECT Id 
                                                       FROM @Keywords 
                                                       WHERE Name = 'ALFA')
                                 AND kac1.Category_Id = c.Id
INNER JOIN 
    @KeywordAdCategories AS kac2 ON kac1.Ad_Id = kac2.Ad_Id 
                                 AND kac2.Keyword_Id = (SELECT Id 
                                                        FROM @Keywords 
                                                        WHERE Name = '147')
                                 AND kac2.Category_Id = c.Id
GROUP BY
    c.Id, c.Name

Result set is:

 Id          Name       count
 ----------- ---------- -----------
 1           Carros     1

Is this what you want?

Answer 2

You can use the Distinct() method.

var query = ...
var query = query.Distinct();

See This code returns distinct values. However, what I want is to return a strongly typed collection as opposed to an anonymous type for more details.

Answer 3

Split the query string into an array and iterate through querying the database for each keyword and joining the result sets using unions. The resultant set will be every distinct record that matches any of the given keywords.

Answer 4

Maybe this is close? At least the subqueries open it up a little for you to work with.

var query =
  from c in categoryQuery
  let keywords =
  (
    from k in keywordQuery where splitKeywords.Contains(k.Name)
    join kac in keywordAdCategoryQuery on k.Id equals kac.Keyword_Id
    where kac.Category_Id == c.Id
    join a in adQuery on kac.Ad_Id equals a.Id
    select k.Id
  ).Distinct()
  where keywords.Any()
  select new CategoryListByKeywordsDetailDto
  {
    Id = c.Id,
    Name = c.Name,
    SearchCount =
    (
      from kac in keywordAdCategoryQuery
      where kac.Category_Id == c.Id
      join kId in keywords on kac.Keyword_Id equals kId
      select kac.Id
    ).Distinct().Count(),
    ListController = c.ListController,
    ListAction = c.ListAction
  };