c# - 包含关键字的 Linq 不同记录

Question

我需要根据汽车关键字搜索返回不同的记录列表，例如:“Alfa 147”

问题是，因为我有 3 辆“Alfa”汽车，它返回 1 + 3 条记录(Alfa 和 147 结果似乎是 1，Alfa 结果似乎是 3)

编辑:

SQL-Server 查询看起来像这样:

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

我的 LINQ 查询是:

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

实体是:

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

我期望搜索“ALFA”的结果是“Cars: 3”，搜索“ALFA 147”的结果是“Cars: 1”，实际上我得到的结果是“Cars: 1\n Cars: 3"

Answer 1

kac 没有过滤词...所以 kac、kac1 和 kac2 的连接将返回 3 行，因为这是该广告的关键字数

你应该删除它..

试试这个:

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

我做了一个测试...

将环境设置为

    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)

我运行这两个查询:

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

我得到了这个结果集:

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

第二个查询是两个词...

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

结果集是:

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

这是你想要的吗？