ASP.NET MVC 5中对数据表的筛选器后端不区分重音

Question

我在后端创建了一个方法来处理数据表的过滤器。

另一方面，这个不掌握法语的口音，所以如果我有"école"；我写"；ecole"；它找不到它。

我在stackoverflow 的另一个问题上发现了这种方法

public static String RemoveDiacritics(this String s)
{
String normalizedString = s.Normalize(NormalizationForm.FormD);
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < normalizedString.Length; i++)
{
Char c = normalizedString[i];
if (CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
{
stringBuilder.Append(c);
}
}
return stringBuilder.ToString().Normalize(NormalizationForm.FormC);
}

它是有效的，但只是针对我的部分问题。它适用于搜索中所写的字母或单词，但我无法将其应用于linq查询，因此使用.RemoveDiacritics()方法我的"；école"；变成"；ecole"；，但我不能将其应用于我的表的列中，并且它总是查找"；école"；。

这里的搜索代码：

if (search != null)
{
int n;
search = search.Trim();
var isNumeric = int.TryParse(search, out n);
if (isNumeric)
{
IdFilter = n;
query = query.Where(x => x.UsagerId == IdFilter || x.Niveau == IdFilter);
}
else if (search != "")
{
// this line work
textFilter = search.ToLower().RemoveDiacritics();
// This is the full line, but absolutely takes the accents out to get the right information out
// query = query.Where(x => x.Nom.ToLower().Contains(textFilter) || x.Prenom.ToLower().Contains(textFilter) || x.Username.ToLower().Contains(textFilter) || x.Email.ToLower().Contains(textFilter) || x.EtabNom.ToLower().Contains(textFilter) || x.ActifStatut.ToLower().Contains(textFilter));

// This is the line that will replace the line above, which I try and it doesn't work ( this part: x.Prenom.ToLower().RemoveDiacritics())
query = query.Where(x => x.Prenom.ToLower().RemoveDiacritics().Contains(textFilter)); 
}
}

这是基本查询：

IQueryable<ListeUsagers> query = (from u in db.USAGERs
join e in db.ETABLISSEMENTs on u.USAGER_INST equals e.ETAB_CODE
where u.USAGER_INST == instId && u.USAGER_NIVEAU > 3 && u.USAGER_NIVEAU < 5 //&& u.USAGER_ACTIF == 1
select new ListeUsagers()
{
UsagerId = u.USAGER_id,
Nom = u.USAGER_NOM,
Prenom = u.USAGER_PRENOM,
EtabCode = e.ETAB_CODE,
EtabNom = e.ETAB_NOM_COURT,
EtabType = e.ETAB_TYPE,
Niveau = u.USAGER_NIVEAU,
Username = u.USAGER_USERNAME,
UserPassword = u.USAGER_MP,
DateCreation = u.USAGER_DATE_INSC,
Sexe = u.USAGER_SEXE,
Lang = u.USAGER_LANGUE,
Telephone = u.USAGER_TELEPHONE,
Email = u.USAGER_EMAIL,
FonctionTravail = u.USAGER_FONCTION,
LieuTravail = u.USAGER_LIEUTRAVAIL,
Note = u.USAGER_NOTE,
Actif = u.USAGER_ACTIF,
ActifStatut = u.USAGER_ACTIF == 0 ? "Inactif" : "Actif"
});

这就是错误：

LINQ to Entities不识别方法"System.String RemoveDiacritics(System.String("方法，并且此方法无法转换为存储表达式。

Answer 1

实体框架中有内置的功能：https://learn.microsoft.com/en-us/ef/core/miscellaneous/collations-and-case-sensitivity如果您使用EF 5+

你会想要一个不区分重音的排序规则(在该页的例子中是"AI"，而不是"AS"(