Linq在不同表的联接字段中使用“和"运算符搜索文本 [英] Linq search text using 'and' operator in joined fields from different tables
问题描述
感谢NetMage在使用和"运算符的Linq搜索文本中回答我的问题
Thanks NetMage for answering my question in Linq search text using 'and' operator
现在,请让我问一些更复杂的问题:
Now, please let me ask some more complicated:
我有以下表格,其中localizationdata.EntryId
链接到mascotcategories.Id
和mascots.Id
,具体取决于localizationdata.Type
值.
I have the following tables where localizationdata.EntryId
is linked to mascotcategories.Id
and mascots.Id
depending on the localizationdata.Type
value.
`localizationdata` (
`Id` int(11)`, Type` varchar(45),
`EntryId` int(11),
`LanguageId` int(11),
`Value` varchar(150) }
`mascots` (
`Id` int(11), `Name` varchar(100),
`MascotCategoryId` int(11),
`UserId` varchar(100),
`mascotcategories` (
`Id` int(11), `CategoryName` varchar(45),
`IsVisible` bit(1),
`ParentId` int(11) DEFAULT NULL,
`SawgrassOnly` bit(1)
我需要再次与"搜索(如在上面的链接中的上一个问题一样)localizationdata.value
,其中包含mascotcategories.CategoryName
和mascots.Name
的翻译词.
I need again to 'AND' search (as in my previous question in the link above) the localizationdata.value
which contains the translated word for mascotcategories.CategoryName
and mascots.Name
.
例如,用户将输入法语关键词"bleu ici",其中"bleu"来自具有翻译类别名称的localizationdata.Value
,而"ici"也来自具有丰富吉祥物的翻译名称的localizationdata.Value
.我需要既包含上述名称又包含类别名称的吉祥物.这可行吗?
For example, the user would enter French keywords "bleu ici" in which the 'bleu ' comes from localizationdata.Value
that has the translated category name and 'ici' comes from localizationdata.Value
too that has the mascot's translated name. I need the mascots that contain both the above words to their name and to their categories' name. Is this feasible?
推荐答案
我不确定这是如何优化的,但我相信它会起作用:
I am not sure how optimal this is, but I believe it works:
首先,将两种本地化类型简化为一种常见类型:
First, reduce the two types of localization down to one common type:
var mascotNameLocalized = from ld in db.localizationdata
where ld.Type == "mascot"
group ld by ld.EntryId into ldg
select new { MascotId = ldg.Key, Words = ldg.Select(g => g.Value) };
var mascotCategoriesLocalized = from ld in db.localizationdata
where ld.Type == "mascotcategory"
join mc in db.mascotcategories on ld.EntryId equals mc.Id
join m in db.mascots on mc.Id equals m.MascotCategoryId into mj
where mj.Any()
from m in mj
group ld by m.Id into ldg
select new { MascotId = ldg.Key, Words = ldg.Select(g => g.Value) };
然后将它们合并为一个常见列表:
Then combine them into one common list:
var mascotsLocalized = from m in mascotNameLocalized.Concat(mascotCategoriesLocalized)
group m by m.MascotId into mg
select new { MascotId = mg.Key, Words = mg.SelectMany(m2 => m2.Words).ToList() };
然后找到与所有搜索词匹配的所有吉祥物:
Then find all mascots that match all the search terms:
var srch = "bleu ici";
var srchwords = srch.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
var ans = from m in mascotsLocalized
where srchwords.All(sw => m.Words.Contains(sw))
select m.MascotId;
这篇关于Linq在不同表的联接字段中使用“和"运算符搜索文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!