需要有关将 ORM 和 SQL 与遗留系统相结合的建议
我们正在将遗留系统移植到.NET，不仅是为了清理架构，也是为了利用许多在遗留系统中不容易实现的新可能性。
注意：在提交之前阅读我的帖子时，我注意到我可能在某些地方描述的有点太快了，即。 掩盖了细节。 如果有任何不清楚的地方，请留下评论（不是答案），我会尽可能地补充
遗留系统使用数据库，并且到处都是 100% 自定义编写的 SQL。 这导致了宽表（即许多列），因为需要数据的代码仅检索作业所需的内容。
作为移植的一部分，除了自定义 SQL 之外，我们还引入了一个可以使用的 ORM 层。 我们选择的 ORM 是 DevExpress XPO，它的一个特性也给我们带来了一些问题，即当我们为 Employee 表定义一个 ORM 类时，我们必须为所有列添加属性，否则它不会为我们检索它们。
这也意味着当我们检索 Employee 时，我们会获得所有列，即使我们只需要几个列。
使用 ORM 的一个好处是我们可以将一些与属性相关的逻辑放入同一个类中，而不必在各处重复它。 例如，可以将将名字、中间名和姓氏组合成“显示名称”的简单表达式放在那里作为示例。
然而，如果我们在某个地方编写 SQL 代码，无论是在类似 DAL 的构造中，还是在任何地方，我们都需要复制此表达式。 这感觉不对，看起来像是错误和维护噩梦的根源。
然而，由于我们有两个选择：
- ORM，获取所有内容，可以将逻辑编写一次
- SQL，获取我们需要的内容，需要复制逻辑
然后我们想出了一个替代方案。 由于 ORM 对象是从字典中代码生成的，因此我们决定也生成一组哑类。 它们将具有相同数量的属性，但不会以相同的方式绑定到 ORM。 此外，我们还为所有生成的对象添加了接口，并使 ORM 和虚拟对象都实现了该接口。
这使我们能够将一些逻辑移出到与接口相关的扩展方法中。 由于哑对象携带了足够的信息，让我们可以将它们插入到我们的 SQL 类中，并且我们可以获取一个列表（具有可用的逻辑），而不是获取数据表，这看起来是有效的。
然而，这导致了另一个问题。 如果我想编写一段代码，仅在我需要知道他们是谁（即他们在系统中的标识符）以及他们的姓名（名字、中间名和姓氏）的上下文中显示或处理员工，如果我使用这个愚蠢的对象，编译器无法保证调用我的代码确实提供了所有这些东西。
一种解决方案是让对象知道哪些属性已被赋值，并且尝试读取未赋值的属性会因异常而崩溃。 这使我们有机会在运行时捕获代码未传递足够信息的合同违规行为。
这对我们来说也看起来很笨重。
因此，基本上我想要的建议是，如果其他人曾经或正在经历这种情况，以及您可以提供的任何提示或建议。
目前我们还不能打破局面。 由于端口的大小，遗留应用程序仍将存在多年，并且 .NET 代码不是 3 年内发布类型的项目，但将在此过程中分阶段发布。 因此，遗留系统和 .NET 代码都需要使用相同的表。
我们也知道这不是一个理想的解决方案，因此请不要提出“您不应该这样做”之类的建议。 我们很清楚这一点:)
我们研究的一件事是使用“契约”创建一个 XML 文件或类似文件。 因此，我们可以在这个 XML 文件中放入如下内容：
- 有一个 Employee 类，具有这 50 个属性
- 此外，我们还有这 7 个变体，用于程序的各个部分
- 另外，我们有这 10 条逻辑，每个逻辑都需要属性 X 、Y 和 Z（X、Y 和 Z 在这 10 个之间变化）
这可以让我们通过代码生成这 8 个类（全类 + 7 个较小的变体），并让生成器检测变体 #3、属性 X、Y并且 K 存在，然后我可以将逻辑代码或逻辑所需的接口自动绑定到此类中。 这将使我们能够拥有许多不同类型的员工类，具有不同程度的属性覆盖范围，并且让生成器自动向其中添加此类支持的所有逻辑。
然后我的代码可以说我需要一个 IEmployeeWithAddressAndPhoneNumbers 类型的员工。
这看起来也很笨重。
We are in the process of porting a legacy system to .NET, both to clean up architecture but also to take advantage of lots of new possibilities that just aren't easily done in the legacy system.
Note: When reading my post before submitting it I notice that I may have described things a bit too fast in places, ie. glossed over details. If there is anything that is unclear, leave a comment (not an answer) and I'll augment as much as possible
The legacy system uses a database, and 100% custom written SQL all over the place. This has lead to wide tables (ie. many columns), since code that needs data only retrieves what is necessary for the job.
As part of the port, we introduced an ORM layer which we can use, in addition to custom SQL. The ORM we chose is DevExpress XPO, and one of the features of this has also lead to some problems for us, namely that when we define a ORM class for, say, the Employee table, we have to add properties for all the columns, otherwise it won't retrieve them for us.
This also means that when we retrieve an Employee, we get all the columns, even if we only need a few.
One nice thing about having the ORM is that we can put some property-related logic into the same classes, without having to duplicate it all over the place. For instance, the simple expression to combine first, middle and last name into a "display name" can be put down there, as an example.
However, if we write SQL code somewhere, either in a DAL-like construct or, well, wherever, we need to duplicate this expression. This feels wrong and looks like a recipe for bugs and maintenance nightmare.
However, since we have two choices:
- ORM, fetches everything, can have logic written once
- SQL, fetches what we need, need to duplicate logic
Then we came up with an alternative. Since the ORM objects are code-generated from a dictionary, we decided to generate a set of dumb classes as well. These will have the same number of properties, but won't be tied to the ORM in the same manner. Additionally we added interfaces for all of the objects, also generated, and made both the ORM and the dum objects implement this interface.
This allowed us to move some of this logic out into extension methods tied to the interface. Since the dumb objects carry enough information for us to plug them into our SQL-classes and instead of getting a DataTable back, we can get a List back, with logic available, this looks to be working.
However, this has lead to another issue. If I want to write a piece of code that only displays or processes employees in the context that I need to know who they are (ie. their identifier in the system), as well as their name (first, middle and last), if I use this dumb object, I have no guarantee by the compiler that the code that calls me is really providing all this stuff.
One solution is for us to make the object know which properties have been assigned values, and an attempt to read an unassigned property crashes with an exception. This gives us an opportunity at runtime to catch contract breaches where code is not passing along enough information.
This also looks clunky to us.
So basically what I want advice on is if anyone else has been in, or are in, this situation and any tips or advice you can give.
We can not, at the present time, break up the tables. The legacy application will still have to exist for a number of years due to the size of the port, and the .NET code is not a in-3-years-release type of project but will be phased in in releases along the way. As such, both the legacy system and the .NET code need to work with the same tables.
We are also aware that this is not an ideal solution so please refrain from advice like "you shouldn't have done it like this". We are well aware of this :)
One thing we've looked into is to create an XML file, or similar, with "contracts". So we could put into this XML file something like this:
- There is an Employee class with these 50 properties
- Additionally, we have these 7 variations, for various parts of the program
- Additionally, we have these 10 pieces of logic, that each require property X, Y and Z (X, Y and Z varies between those 10)
This could allow us to code-generate those 8 classes (full class + 7 smaller variations), and have the generator detect that for variation #3, property X, Y and K is present, and I can then tie in either the code for the logic or the interfaces the logic needs into this class automagically. This would allow us to have a number of different types of employee classes, with varying degrees of property coverage, and have the generator automatically add all logic that would be supported by this class to it.
My code could then say that I need an employee of type IEmployeeWithAddressAndPhoneNumbers.
This too looks clunky.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我建议最终可能需要进行数据库重构（规范化）。 您可以进行重构并使用视图为遗留应用程序提供与其期望一致的数据库接口。 也就是说，例如，将员工表分解为员工信息、员工联系人信息、员工分配，然后为遗留应用程序提供一个名为员工的视图，该视图在这三个表之间进行联接（或者如果逻辑是基于表的函数）更复杂）。 这可能会让您继续使用完全基于 ORM 的解决方案，这正是我所希望的，并使您的遗留应用程序保持满意。 我不会继续使用 ORM/直接 SQL 的混合解决方案，尽管您可以通过一些实体类来增强您的 ORM，这些实体类提供相同数据的不同视图（例如，跨几个表进行联接以进行只读显示） ）。
I would suggest that eventually a database refactoring (normalization) is probably in order. You could work on the refactoring and use views to provide the legacy application with an interface to the database consistent with what it expects. That is, for example, break the employe table down in to employee_info, employee_contact_info, employee_assignments, and then provide the legacy application with a view named employee that does a join across these three tables (or maybe a table-based function if the logic is more complex). This would potentially allow you to move ahead with a fully ORM-based solution which is what I would prefer and keep your legacy application happy. I would not proceed with a mixed solution of ORM/direct SQL, although you might be able to augment your ORM by having some entity classes which provide different views of the same data (say a join across a couple of tables for read-only display).
“目前我们无法分解这些表。由于端口的大小，遗留应用程序仍将存在很多年，并且 .NET 代码不会在 3 年内完成 -发布类型的项目，但将在发布过程中分阶段实施，因此遗留系统和 .NET 代码都需要使用相同的表。”
两个词：物化视图。
您有多种“就地标准化”的方法。
物化视图，又名索引视图。 这是源表的规范化克隆。
从旧表显式复制到新表。 你说“恶心”。 但是，请考虑您将逐步从旧应用程序中删除功能。 这意味着您将在新的规范化表中拥有一些功能，并且可以正常地忽略旧表。
显式 2 路同步。 这很难，但并非不可能。 您可以通过从旧表复制到正确设计的表来进行标准化。 作为临时解决方案，您可以使用存储过程和触发器将事务克隆到旧表中。 然后，您可以在转换过程中消除这些杂凑。
您会很乐意在两个完全不同的模式中执行此操作。 由于旧数据库可能没有设计良好的架构，因此您的新数据库将具有一个或多个命名架构，以便您可以对定义进行某种版本控制。
"We can not, at the present time, break up the tables. The legacy application will still have to exist for a number of years due to the size of the port, and the .NET code is not a in-3-years-release type of project but will be phased in in releases along the way. As such, both the legacy system and the .NET code need to work with the same tables."
Two words: materialized views.
You have several ways of "normalizing in place".
Materialized Views, a/k/a indexed views. This is a normalized clone of your source tables.
Explicit copying from old tables to new tables. "Ick" you say. However, consider that you'll be incrementally removing functionality from the old app. That means that you'll have some functionality in new, normalized tables, and the old tables can be gracefully ignored.
Explicit 2-way synch. This is hard, not not impossible. You normalize via copy from your legacy tables to correctly designed tables. You can -- as a temporary solution -- use Stored Procedures and Triggers to clone transactions into the legacy tables. You can then retire these kludges as your conversion proceeds.
You'll be happiest to do this in two absolutely distinct schemas. Since the old database probably doesn't have a well-designed schema, your new database will have one or more named schema so that you can maintain some version control over the definitions.
虽然我没有使用过这个特定的 ORM，但在某些情况下，视图在提供轻量级对象以在这些类型的数据库中显示和报告方面很有用。 根据他们的文档，他们确实支持这样一个概念： XPView Concepts
Although I haven't used this particular ORM, views can be useful in some cases in providing lighter-weight objects for display and reporting in these types of databases. According to their documentation they do support such a concept: XPView Concepts