数据库数据的LINQ本地化/的I18n到SQL [英] Localisation/I18n of database data in LINQ to SQL
问题描述
我在我的数据库状态表，以及包含这些状态的特定语言版本的本地化表。主状态表的点是定义有关状态的状态ID值，以及其它元数据。在本地化表中显示的文本再presentation在用户界面，根据用户的preferred语言。下面是一个例子模式：
CREATE TABLE [语言]
（
ID SMALLINT主键，
ISOName VARCHAR（12）
）
创建表的employeeStatus
（
ID SMALLINT主键，
code VARCHAR（50）
）
创建表EmployeeStatusLocalised
（
EmployeeStatusID SMALLINT，
LanguageID SMALLINT，
说明VARCHAR（50）
约束PK_EmployeeStatusLocalised主键
（EmployeeStatusID，LanguageID）
约束FK_EmployeeStatusLocalised_EmployeeStatus外键
（EmployeeStatusID）引用的employeeStatus（ID）
约束FK_EmployeeStatusLocalised_Language外键
（LanguageID）参考文献[语言]（ID）
）
创建Employee表
（
ID INT标识（1,1）主键，
EmployeeName VARCHAR（50）NOT NULL，
EmployeeStatusID SMALLINT NOT NULL，
约束FK_Employee_EmployeeStatus外键
（EmployeeStatusID）引用的employeeStatus（ID）的
）
这是我怎么会通常访问的数据：
选择e.EmployeeName，esl.Description作为的employeeStatus
从员工Ë
内部联接EmployeeStatusLocalised ESL上
e.EmployeeStatusID = esl.EmployeeStatusID和esl.LanguageID = 1
我不是真的很高兴，我的LINQ to SQL正在做的事情，在最有效的方式，但。这里有一个例子：
使用（VAR上下文=新MyDbDataContext（））
{
VAR项目=（从context.Employees记录
选择记录）。取（1）.SingleOrDefault（）;
Console.WriteLine（{0}：{1}，item.EmployeeName，
item.EmployeeStatus.EmployeeStatusLocaliseds。
其中（ESL =＆GT; esl.LanguageID == 1）。单（）的说明）。
}
就个人而言，我可能会离开的employeeStatus codeS中的数据库，并把所有的本地化逻辑到客户端。如果这是一个Web应用程序（ASP.NET或ASP.NET MVC），那么你会用的的employeeStatus code作为重点成一个资源文件，然后用的UICulture =自动和文化=自动告诉ASP.NET拿起基于接受语言HTTP头合适的资源。
您会提供默认嵌入在您的应用程序（文化不敏感）的资源，并允许的SATAlite组件覆盖，他们所需要的默认值。
这个问题，对我来说，与添加本地化到数据库是，你先结了更为复杂的查询，你必须保持泵的区域设置为每个查询，并且无法缓存的输出查询，以便广泛。其次，你必须持有实体和持有本地化表表的mixure。最后，DBA需要做本地化。
在理想情况下，你希望有人谁知道如何翻译文本做本地化，并为他们使用一些工具，他们是舒服。有很多的.resx工具，在那里，和应用程序，使语言专家做自己的事情。
如果你坚持用数据库表的定位，因为那是怎么回事，那么也许你应该单独查询查找到真实的数据，并加入两个在UI。这将至少给你在未来的升级到的.resx。
您应该检查出盖伊·史密斯，费里尔的书上的国际化，如果你有兴趣在这方面的：
I have status tables in my database, and "localised" tables that contain language-specific versions of those statuses. The point of the main status table is to define the status ID values, and other meta-data about the status. The "localised" table is to display the text representation in a UI, according to the users' preferred language. Here is an example schema:
create table [Language]
(
ID smallint primary key,
ISOName varchar(12)
)
create table EmployeeStatus
(
ID smallint primary key,
Code varchar(50)
)
create table EmployeeStatusLocalised
(
EmployeeStatusID smallint,
LanguageID smallint,
Description varchar(50),
constraint PK_EmployeeStatusLocalised primary key
(EmployeeStatusID, LanguageID),
constraint FK_EmployeeStatusLocalised_EmployeeStatus foreign key
(EmployeeStatusID) references EmployeeStatus (ID),
constraint FK_EmployeeStatusLocalised_Language foreign key
(LanguageID) references [Language] (ID)
)
create table Employee
(
ID int identity(1,1) primary key,
EmployeeName varchar(50) not null,
EmployeeStatusID smallint not null,
constraint FK_Employee_EmployeeStatus foreign key
(EmployeeStatusID) references EmployeeStatus (ID)
)
This is how I'd typically access that data:
select e.EmployeeName, esl.Description as EmployeeStatus
from Employee e
inner join EmployeeStatusLocalised esl on
e.EmployeeStatusID = esl.EmployeeStatusID and esl.LanguageID = 1
I'm not really happy that my LINQ to SQL is doing things in the most efficient way, though. Here's an example:
using (var context = new MyDbDataContext())
{
var item = (from record in context.Employees
select record).Take(1).SingleOrDefault();
Console.WriteLine("{0}: {1}", item.EmployeeName,
item.EmployeeStatus.EmployeeStatusLocaliseds.
Where(esl => esl.LanguageID == 1).Single().Description);
}
Personally, I'd probably leave the EmployeeStatus codes in the DB and move all localization logic into the client. If this is a web app (ASP.NET or ASP.NET MVC) then you'd use the the EmployeeStatus code as a key into a resource file, and then use UICulture="Auto" and Culture="Auto" to tell ASP.NET to pick up the right resources based upon the "Accept-Language" HTTP Header.
You'd provide default (culture insensitive) resources embedded in your app, and allow satalite assemblies to override the defaults where they needed.
The problem, for me, with adding localization into the DB is that you firstly end up with much more complicated queries, you have to keep pumping the locale into each of these queries, and you can't cache the outputs of the queries so widely. Secondly, you have a mixure of tables that hold entities and tables that hold localization. Finally, a DBA is required to do the localization.
Ideally you want someone who understands how to translate text to do the localization, and for them to use some tool that they're comfortable with. There are plenty of .resx tools out there, and apps that allow language experts to "do their thing".
If you're stuck with DB tables for localization because "that's how it is" then perhaps you should query the lookups seperately to the real data, and join the two at the UI. This would at least give you an "upgrade path" to .RESX in the future.
You should check out Guy Smith-Ferrier's book on i18n if you're interested in this area:
这篇关于数据库数据的LINQ本地化/的I18n到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
