Search based on a set of keywords

Question

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.

The search is made in a KeywordSearch Table:

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

Where the Keyword Table is:

public class Keyword
{
    public int Id { get; set; }
    public string Name { get; set; }
}

The Ads are related with the Keywords using the following Table:

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; }
}

Finally, the Category table:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Example:

• Keywords: "Mercedes-Benz" and "GLK"
• KeywordSearch: "Mercedes" and "Benz" for the Keyword "Mercedes-Benz" "GLK" for the Keyword "GLK"
• Category: "Cars" and "Trucks"

• Ads: Car - Mercedes-Benz GLK Truck - Mercedes-Benz Citan

  If I search "Mercedes-Benz" I get:

  • Cars: 1
  • Trucks: 1

  If I search "Mercedes-Benz GLK" I get:

  • Cars: 1

  If I search "Mercedes Citan" I get:

  • Trucks: 1

What I get until now:

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)               <--- 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()
                };

The problem is that I can't get only the Ads that match all Keywords.

EDIT:

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"

Any Help?

Answer 1

this may not be the direct answer, but in such "multiple parameter search" situations i just forget about anything and do the simple thing, for ex: Search By Car Manufacturer, CategoryId, MillageMax, Price :

var searchResults = from c in carDb.Cars
where (c.Manufacturer.Contains(Manufacturer) || Manufacturer == null) &&
                 (c.CategoryId == CategoryId || CategoryId == null) &&
                    (c.Millage <= MillageMax || MillageMax== null) &&
                          (c.Price <= Price  || Price == null) 
select c

now if any of the parameters is null it cancels the containing line by making the whole expression in brackets True and so it does not take a part in search any more