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