包含关键字的LINQ区分记录 [英] Linq distinct record containing keywords

问题描述

我需要返回的基于汽车的关键字记录的不同列表中进行搜索，如：阿尔法147

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

问题是，因为我有3个阿尔法汽车，它返回1 + 3记录（好像1阿尔法和147的结果，和3阿尔法结果）

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)

编辑：

在SQL-Server查询看起来像这样：

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

我的LINQ查询是：

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

该实体是：

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

我期望的搜索阿尔法的结果是赛车总动员3和搜索阿尔法147是赛车总动员1，实际上结果我得到的是赛车总动员：1 \\ n汽车： 3

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"

推荐答案

Fiuu，这是脑残骸。我在几件splited查询，但它是作为一个整体在端（VAR结果）执行。我返回的匿名类，但意图很明显。

Fiuu, this was brain-wreck. I splited query in several pieces, but it's executed as a whole at the end (var result). And I returned anonymous class, but intention is clear.

下面是解决方案：

var keywordIds = from k in keywordQuery
                    where splitKeywords.Contains(k.Name)
                    select k.Id;

var matchingKac = from kac in keywordAdCategories
            where keywordIds.Contains(kac.Keyword_Id)
            select kac;

var addIDs = from kac in matchingKac
                group kac by kac.Ad_Id into d
                where d.Count() == splitKeywords.Length
                select d.Key;

var groupedKac = from kac in keywordAdCategoryQuery
                where addIDs.Contains(kac.Ad_Id)
                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
                {
                    Id = final.Key,
                    Name = c.Name,
                    SearchCount = final.Count()
                };

// here goes result.ToList() or similar

这篇关于包含关键字的LINQ区分记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！