H!
我正在为一个在SQL数据库上使用NHibernate的web应用程序编写一个带有区域设置回退的消息存储。例如,store.Get("Greet", "fr-ca-on")
的结果将是来自该数据的最佳匹配值(fr-ca
):
Key Locale Message
----- ------ --------
Greet Hello
Greet fr Bonjour!
Greet fr-ca Âllo!
Greet fr-ca-qc Âllo toi!
我尝试了各种类似的查询来实现回退:
string[] locales = new[] { "fr-ca-on", "fr-ca", "fr", "" };
return (
from Translation translation in reader.Get<Translation>()
where locales.Contains(translation.Locale)
group translation by translation.Key into translationGroup
select translationGroup.OrderByDescending(p => p.Locale.Length).First()
//or: select translationGroup.MaxBy(p => p.Locale.Length)
);
这些在内存中运行得很好,但NHibernate无法将组选择转换为SQL。(它似乎只支持像.Count()
这样的简单聚合方法,而不支持select。)
如何重写这个查询,以便NHibernate可以将其转换为SQL?我想不出一种方法不需要尽早将查询解除到内存中,这会真正影响应用程序的性能。
(我宁愿不使用资源文件,因为我有其他与翻译相关的实体。)
我能够将其重写为LINQ子查询:
string[] locales = new[] { "fr-ca-on", "fr-ca", "fr", "" };
return (
from Translation translation in reader.Get<Translation>()
where
locales.Contains(translation.Locale)
&& (translation.Locale.Length == reader
.Get<Translation>()
.Where(p => locales.Contains(p.Locale) && p.Key == translation.Key)
.Max(p => p.Locale.Length)
)
select translation
);
NHibernate很好地处理了子查询,并将其转换为以下(经过修饰的)SQL:
SELECT *
FROM [translation] translation
WHERE
[locale] IN ('fr-ca-on', 'fr-ca', 'fr', '')
AND LEN([locale]) = (
SELECT CAST(MAX(LEN(sub.[locale])) AS INT)
FROM [translation] sub
WHERE
sub.[locale] IN ('fr-ca-on', 'fr-ca', 'fr', '')
AND sub.[key] = translation.[key]
)
如果您真的只想实现mentionen和Get方法
怎么样?
private string Get(string key, string locale)
{
return (from Translation t in reader
where t.Key == key && (t.Locale == null || t.Locale == "" || locale.StartsWith(t.Locale))
orderby t.Locale descending
select t.Value).First();
}
PS:更改为"StartsWith"是有意义的,这取决于您拥有的区域设置键和您的请求异国情调,但我现在能想到的唯一例子是:)当请求"ch"(瑞士德语)时,"fr-ch"可能会给你法语