我需要根据汽车关键字搜索返回一个不同的记录列表，如:"Alfa 147"
问题是，因为我有3辆"阿尔法"汽车，它返回1 + 3条记录(阿尔法和147的结果似乎是1，阿尔法的结果是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')
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"
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
可以使用Distinct()方法
var query = ...
var query = query.Distinct();
将查询字符串拆分为一个数组，遍历查询数据库中的每个关键字，并使用联合连接结果集。结果集将是匹配任何给定关键字的所有不同记录。
也许这很接近?至少子查询为您打开了一点空间。
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
};
linq的一个美妙特性是，您可以用更小更简单的步骤构建复杂的查询，并让linq找出如何将它们连接在一起。
下面是获取该信息的一种方法。我不确定这是否是最好的，当选择多个关键字时，您需要检查它的性能是否良好。
假设关键字的定义类似于
var keywords = "Alfa 147";
var splitKeywords = keywords.Split(new char[] {' '});
第一阶段
获取按广告、类别和
分组的关键字列表var subQuery = (from kac in keywordAdCategoryQuery
join k in keywordQuery on kac.Keyword_Id equals k.Id
select new
{
kac.Ad_Id,
kac.Category_Id,
KeyWord = k.Name,
});
var grouped = (from r in subQuery
group r by new { r.Ad_Id, r.Category_Id} into results
select new
{
results.Key.Ad_Id ,
results.Key.Category_Id ,
keywords = (from r in results select r.KeyWord)
});
注意，您发布的类将建议您的数据库在表之间没有定义外键关系。如果他们这样做了，那么这个阶段就会稍微简单一些。
第二阶段过滤掉所有不包含
关键字的组foreach(var keyword in splitKeywords)
{
var copyOfKeyword = keyword ; // Take copy of keyword to avoid closing over loop
grouped = (from r in grouped where r.keywords.Contains(copyOfKeyword) select r) ;
}
第三阶段
按类别分组并计算每个类别的结果
var groupedByCategories = (from r in grouped
group r by r.Category_Id into results
join c in categoryQuery on results.Key equals c.Id
select new
{
c.Id ,
c.Name ,
Count = results.Count()
});
第四阶段
现在从sql中检索信息。这应该在一个查询中完成。
var finalResults = groupedByCategories.ToList();
那么，如果我正确理解了需求，您希望在文本中匹配所有单词子集，而不是您现在获得的OR匹配?我看到至少有两个选项，其中第一个可能不会将分割转换为SQL:
var query = from k in keywordQuery where !splitKeywords.Except(k.Name.split(' ')).Any()
这做了以下假设:
- 关键字中的单词以空格分隔。
- 您正在寻找完全匹配而不是部分匹配。(即Test将不匹配TestTest)。
另一种选择是使用谓词构建器动态生成谓词(有一段时间没有这样做了，我的实现可能需要调整-但这是更可能(在我看来更好)的解决方案):
var predicate = PredicateBuilder.True<keywordQuery>();
foreach (string s in splitKeywords) {
predicate.AND(s.Contains(k.Name));
}
query.Where(predicate);
如果有人可以评论，如果我的一些语法是错误的，我会很感激。EDIT:包含谓词构建器的良好参考链接:http://www.albahari.com/nutshell/predicatebuilder.aspx
谓词构建器跨多个表，如果有人来这里寻找如何做到这一点。PredicateBuilder可以生成跨多个表的谓词吗?
应该可以查询每个关键字，然后将结果集联合起来。重复的值将从联合中删除，您可以计算出所需的聚合。
尝试在选择
时删除类 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
{
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
}).Distinct().ToList();
var searchResults = new CategoryListByBeywordsListDto();
searchResults.CategoryListByKeywordsDetails = (from q in query select new CategoryListByKeywordsDetailDto
{
Id = q.Id,
Name = q.Name,
SearchCount = q.SearchCount,
ListController = q.ListController,
ListAction = q.ListAction
}).ToList();
您正在对CategoryListByKeywordsDetailDto列表进行选择。Distinct只对POCO和匿名对象有效。在您的情况下，您需要实现IEqualitycomparer为select distinct工作
我尝试使用LINQ直接针对内存集合(如在，而不是通过SQL) -似乎对我有用(我认为主要的一点是，你想搜索适用于所有指定的关键字的广告，而不是任何，正确吗?无论如何，下面的一些示例代码(有点像注释，不一定是最有效的，但希望能说明这一点…)
处理以下"数据集":
private List<AdCar> AdCars = new List<AdCar>();
private List<KeywordAdCategory> KeywordAdCategories = new List<KeywordAdCategory>();
private List<Category> Categories = new List<Category>();
private List<Keyword> Keywords = new List<Keyword>();
，使用您提供的数据填充在测试方法中…
搜索方法看起来有点像这样:
var splitKeywords = keywords.Split(' ');
var validKeywords = Keywords.Join(splitKeywords, kwd => kwd.Name.ToLower(), spl => spl.ToLower(), (kwd, spl) => kwd.Id).ToList();
var groupedAdIds = KeywordAdCategories
.GroupBy(kac => kac.Ad_Id)
.Where(grp => validKeywords.Except(grp.Select(kac => kac.Keyword_Id)).Any() == false)
.Select(grp => grp.Key)
.ToList();
var foundKacs = KeywordAdCategories
.Where(kac => groupedAdIds.Contains(kac.Ad_Id))
.GroupBy(kbc => kbc.Category_Id, kac => kac.Ad_Id);
//Results count by category
var catCounts = Categories
.Join(foundKacs, cat => cat.Id, kacGrp => kacGrp.Key, (cat, kacGrp) => new { CategoryName = cat.Name, AdCount = kacGrp.Distinct().Count() })
.ToList();
//Actual results set
var ads = AdCars.Join(groupedAdIds, ad => ad.Id, grpAdId => grpAdId, (ad, grpAdId) => ad);
就像我说的，这更多的是为了说明，请不要太仔细地看join &GroupBy等(不确定这是不是最优的)
所以，使用上面的，如果我搜索"阿尔法"，我得到3个广告结果，如果我搜索"阿尔法147"，我只得到1个结果。
编辑:我已经改变了代码来表示两种可能的结果(因为我不确定你的问题需要哪个)
ads将给出搜索
catCounts将给出一个匿名类型列表，每个类型表示查找结果，按类别表示广告的计数
这有帮助吗?
嗨，如果我理解对了你的问题
"问题是，因为我有3辆"阿尔法"汽车，它返回1 + 3记录(似乎是1的阿尔法和147的结果，和3的阿尔法结果)"
和Linq不是真的需要，我可能有你需要的，只是测试它作为一个新的项目
public Linqfilter()
{
//as Note: I modified a few classes from you because i doesn'T have your Member, Operation, Make,... classes
#region declaration
var originalAdCarList = new List<AdCar>()
{
new AdCar(){Id=1017, Title= "Alfa Romeo 145 1.6TDI 2013", Category= new Category(){Id =12}} ,
new AdCar(){Id=1018, Title= "Alfa Romeo 146 1.6TDI 2013", Category= new Category(){Id =11}} ,
new AdCar(){Id=1019, Title= "Alfa Romeo 147 1.6TDI 2013", Category= new Category(){Id =12}}
};
var originalKeywordAdCategoryList = new List<KeywordAdCategory>()
{
new KeywordAdCategory() { Keyword_Id=1356, Ad_Id=1017,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1356, Ad_Id=1018,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1356, Ad_Id=1019,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1357, Ad_Id=1017,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1357, Ad_Id=1018,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1357, Ad_Id=1019,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1358, Ad_Id=1017,Category_Id=1},
new KeywordAdCategory() { Keyword_Id=1373, Ad_Id=1019,Category_Id=1}
};
var originalCategoryList = new List<Category>()
{
new Category(){Id=1, Name="NULL 1 Carros"},
new Category(){Id=2, Name="NULL 1 Motos"},
new Category(){Id=3, Name="NULL 2 Oficinas"},
new Category(){Id=4 , Name="NULL 2 Stands"},
new Category(){Id=5 , Name="NULL 1 Comerciais"},
new Category(){Id=8, Name="NULL 1 Barcos"},
new Category(){Id=9 , Name="NULL 1 Máquinas"},
new Category(){Id=10 , Name="NULL 1 Caravanas e Autocaravanas"},
new Category(){Id=11 , Name="NULL 1 Peças e Acessórios"},
new Category(){Id=12 , Name="1 1 Citadino"},
new Category(){Id=13 , Name="1 1 Utilitário"},
new Category(){Id=14 , Name="1 1 Monovolume"}
};
var originalKeywordList = new List<Keyword>()
{
new Keyword(){Id=1356 ,Name="ALFA"},
new Keyword(){Id=1357 ,Name="ROMEO"},
new Keyword(){Id=1358 ,Name="145"},
new Keyword(){Id=1373 ,Name="147"}
};
#endregion declaration
string searchText = "ALFA";
// split the string searchText in an Array of substrings
var splitSearch = searchText.Split(' ');
var searchKeyList =new List<Keyword>();
// generate a list of Keyword based on splitSearch
foreach (string part in splitSearch)
if(originalKeywordList.Any(key => key.Name == part))
searchKeyList.Add(originalKeywordList.First(key => key.Name == part));
// generate a list of KeywordAdCategory based on searchKList
var searchKACList = new List<KeywordAdCategory>();
foreach(Keyword key in searchKeyList)
foreach (KeywordAdCategory kAC in originalKeywordAdCategoryList.Where(kac => kac.Keyword_Id == key.Id))
searchKACList.Add(kAC);
var groupedsearchKAClist = from kac in searchKACList group kac by kac.Keyword_Id;
var listFiltered = new List<AdCar>(originalAdCarList);
//here starts the real search part
foreach (IGrouping<int, KeywordAdCategory> kacGroup in groupedsearchKAClist)
{
var listSingleFiltered = new List<AdCar>();
// generate a list of AdCar that matched the current KeywordAdCategory filter
foreach (KeywordAdCategory kac in kacGroup)
foreach (AdCar aCar in originalAdCarList.Where(car => car.Id == kac.Ad_Id))
listSingleFiltered.Add(aCar);
var tempList = new List<AdCar>(listFiltered);
// iterrates over a temporary copie of listFiltered and removes items which don't match to the current listSingleFiltered
foreach (AdCar aC in tempList)
if (!listSingleFiltered.Any(car => car.Id == aC.Id))
listFiltered.Remove(aC);
}
var AdCarCount = listFiltered.Count; // is the count of the AdCar who match
var CatDic =new Dictionary<Category, int>(); // will contain the Counts foreach Categorie > 0
foreach(AdCar aCar in listFiltered)
if(originalCategoryList.Any(cat => cat.Id ==aCar.Category.Id))
{
var selectedCat = originalCategoryList.First(cat => cat.Id == aCar.Category.Id);
if (!CatDic.ContainsKey(selectedCat))
{
CatDic.Add(selectedCat, 1);//new Category Countvalue
}
else
{
CatDic[selectedCat]++; //Category Countvalue +1
}
}
}
}
public class Keyword
{
// Primary properties
public int Id { get; set; }
public string Name { get; set; }
}
public class Category
{
// Primary properties
public int Id { get; set; }
public string Name { get; set; }
}
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; }
}
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 string Member { get; set; }
public Category Category { get; set; }
public IList<string> Features { get; set; }
public IList<int> Pictures { get; set; }
public IList<string> Operations { get; set; }
}
public class AdCar : Ad
{
public int Kms { get; set; }
public string Make { get; set; }
public int Model { get; set; }
public int Fuel { get; set; }
public int Color { get; set; }
}
希望它能帮助到你或其他人
编辑:
扩展了我的方法Linqfilter()来回答请求
Edit2:
我想这应该正是你要找的
var selectedKWLinq = from kw in originalKeywordList
where splitSearch.Contains(kw.Name)
select kw;
var selectedKACLinq = from kac in originalKeywordAdCategoryList
where selectedKWLinq.Any<Keyword>(item => item.Id == kac.Keyword_Id)
group kac by kac.Keyword_Id into selectedKAC
select selectedKAC;
var selectedAdCar = from adC in originalAdCarList
where (from skAC in selectedKACLinq
where skAC.Any(kac => kac.Ad_Id == adC.Id)
select skAC).Count() == selectedKACLinq.Count()
select adC;
var selectedCategorys = from cat in originalCategoryList
join item in selectedAdCar
on cat.Id equals item.Category.Id
group cat by cat.Id into g
select g;
//result part
var AdCarCount = selectedAdCar.Count();
List<IGrouping<int, Category>> list = selectedCategorys.ToList();
var firstCategoryCount = list[0].Count();
var secoundCategoryCount = list[1].Count();
天哪，这简直是脑残。我将查询拆分为几个部分，但它在最后作为一个整体执行(var result)。我返回了匿名类，但意图是明确的。
解决方案如下:
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