复杂（多模式）实体框架对象映射，用于办公室管理应用程序的无缝集成（和最终替换） [英] Complex (Multi-Schema) Entity Framework object mapping for seamless integration (and eventual replacement) of an office management application
问题描述
首先，一些上下文：
我目前在一个非常小的（10名雇员）专科医师办公室中的所有交易工作使我处于一个独特的地位，我有自由的统治（和适度的资源支持）开发和执行任何种类的应用/工具，没有任何种类的需求或压力，因为当前系统的功能足够好。
我们目前运行一个相当日期（约〜2008年）的医疗办公室管理系统，负责处理患者业务账户，结算和保险索赔提交。办公室本身是相当危险的网络。我们有几台使用标准DICOM的诊断机，但大多数医疗记录仍然是纸张。
我可能比我现在可以咀嚼我的愿望，但是我计划慢慢地开发一个更加全面的域名驱动的应用程序，将电子病历（评估/管理和DICOM诊断）与医疗办公室管理端相结合。一旦我有这样的应用程序的框架，我的情况的沙箱方面引起了我的兴趣，我可以探索和开发任何一种自动化或工具，我可以梦想。
有些诚实：
我的经验在这样的事情上非常有限，但我是一个非常坚定的个人和我喜欢应用程序驱动的学习。
我的问题：
我正在开始与当前数据库集成的病人帐户。一切都存储在FoxPro 2.5的免费表中。表之间的关系并不是隐含的，而是隐含的。理想情况下，我希望将具有POCO的新应用程序与EF映射到SQL数据库。这个部分很简单，但是我也希望将同一个POCO映射到目前的FoxPro 2.5 .dbfs（它不一定具有相同的模式）在一个单独的DBContext中。
这是可能的，我正在设想吗？我一直在测试导出所有流畅API映射的水域（ .ToTable（）， .HasColumnName（）等等），但这是一项相当艰巨的任务，我希望在潜水前进之前有一些更有经验的洞察力。我没有找到任何有关我试图尝试的人的相关例子，这也是有些不好意思。
也许我的做法是错误的。我愿意相应地进行调整，但我喜欢在我的应用程序中使用POCO的想法，对于我的新应用程序来说，可以与旧的数据库进行交谈，而不会实现其不直观的架构，这一点非常重要。
所有头痛的主要目的是保持当前应用程序的全部功能，同时允许我运行和开发我的新应用程序。
简而言之：
是否可以使用EF将新的OOR /域驱动应用程序与轻微不同架构的旧数据库集成？如果是这样，有什么提示或例子让我开始？如果没有，我有其他类似功能的替代品吗？谢谢
编辑1：
我要去请参考当前使用的应用程序作为App X从现在开始。
App X的前身运行在Unix上，并坐在FoxPro / xBASE表上，所以App X建立在顶部这可能是使客户升级的琐碎。 App X目录还包含Visual FoxPro 6 .dll以及带有FoxPro .ico的应用程序文件，名称为fbaseeng，该文件将显示名为DTS Command Prompt的命令提示符窗口。我不知道应用程序X如何勾选，但是'DTS'停留在我身上，我花了一些时间看看有没有办法可以使用他们已经实现的任何数据转换，但是我最终放弃了。 >
当前数据库是 231 .dbf 表的集合。幸运的是，他们的很大一部分似乎没有被完全使用，或者仅仅是在一些循环的临时方式中使用，它们不会在App X的运行时间之外存储记录。
几个表似乎是链接表，其他部分包含引用数据，如类型限定符属性
public partial class Accttype
{
public decimal Acct_Type {get;组; }
public string Acct_Desc {get;组; }
public string Sb {get;组;
public decimal费用{get;组; }
public bool Acptasgn {get;组; }
public decimal Insclass1 {get;组; }
public decimal Insclass2 {get;组; }
public decimal Insclass3 {get;组; }
public decimal Insclass4 {get;组; }
public decimal Insclass5 {get;组; }
public string Acct_Grp {get;组;
}
和静态参考值，如邮政编码
public partial class Zip
{
public string Zipcode {get;组; }
public string City {get;组; }
public string St {get;组; }
public string Areacode {get;组; }
public decimal Ext_From {get;组; }
public decimal Ext_To {get;组; }
}
到目前为止，我最关心的是以下表格：
- Patdemo.dbf
包含所有患者访问实践的记录。它有大约100
列，并包含大量信息，包括名称，地址，保险
类型，运行帐户余额总计等。简单的主键是
患者ID，但格式为0.0。
- Billing.dbf
包含与患者相关的字母数字ID索引帐单具体日期为
服务。大约80列，主要包括外键/类型限定符和状态
指标（即 INS1_SENT ）。患有ID的FK
- Charges.dbf
包含每个帐单下的行项目。它是每个遗产的表或
a加入收费和过帐/付款，因为它包含由 C 或
P
在类型列。似乎没有一个简单的主键，但收费有
ChargeID 和过帐/付款有 PostID ，均格式为 BillID +000N。然而，对于
抛出一个曲线球，排除调整没有 ChargeID / PostID 。有FK
的 BillID 。
- Insur.dbf
包含保险提供者和从地址到电子帐单ID的信息。
主键是字母数字ICode（例如：BC01）。
- Patins.dbf
似乎是一个链接表，还包含病人的保险信息
政策的ID号。具有患者ID的FK和 ICode 。
还有各种其他引用表，我将要保持并发性（我们现在还没有完全设计出我的新应用程序的架构，但是我知道无论与病人或保险公司联系在一起，Addresses将是一个具体的类型，这将是更合乎逻辑的。
为了这个例子，我们来看看在先前存在的 Patins.dbf POCO（它是最小的表之一）：
public partial class Patins
{
public decimal Custid {get;组; }
public decimal Inskey {get;组; }
public string Insurcode {get;组; }
public string Insurnum {get;组; }
public string Groupnum {get;组; }
public string Guarlname {get;组; }
public string Guarfname {get;组; }
public string Guarmi {get;组; }
public string Guargen {get;组; }
public string Guaraddr {get;组; }
public string Guaraddr2 {get;组; }
public string Guarcity {get;组; }
public string Guarst {get;组; }
public string Guarzip {get;组; }
public string Guarcountr {get;组; }
public string Guarphone {get;组; }
public string Guaremail {get;组; }
public System.DateTime Guardob {get;组; }
public string Guarsex {get;组; }
public string Guaremp {get;组; }
public decimal Relation {get;组; }
public System.DateTime Startdate {get;组; }
public System.DateTime Enddate {get;组; }
public bool Active {get;组; }
public string Bcpc {get;组; }
public string Auth1 {get;组; }
public string Auth2 {get;组; }
public string Auth3 {get;组; }
public decimal Billcnt {get;组; }
public string Desc1 {get;组; }
public string Desc2 {get;组; }
public string Desc3 {get;组; }
public decimal Visits1 {get;组; }
public decimal Visits2 {get;组; }
public decimal Visits3 {get;组; }
public System.DateTime From1 {get;组; }
public System.DateTime From2 {get;组; }
public System.DateTime From3 {get;组; }
public System.DateTime To1 {get;组; }
public System.DateTime To2 {get;组; }
public System.DateTime To3 {get;组; }
public string Insnote {get;组; }
public string Char1 {get;组; }
public string Char2 {get;组; }
public string Char3 {get;组; }
public string Char4 {get;组; }
public string Char5 {get;组; }
public string Char6 {get;组; }
public string Char7 {get;组; }
public string Char8 {get;组; }
public string Char9 {get;组; }
public string Char10 {get;组; }
public System.DateTime Date1 {get;组; }
public System.DateTime Date2 {get;组; }
public decimal Num1 {get;组; }
public decimal Num2 {get;组; }
public string Createby {get;组; }
public System.DateTime Createdt {get;组; }
public string Modifyby {get;组; }
public System.DateTime Modifydt {get;组; }
public string Cobmemo {get;组; }
public System.DateTime Dinju {get;组; }
public System.DateTime Dsimbd {get;组; }
public System.DateTime Dsimed {get;组; }
public string Createtm {get;组; }
public string Modifytm {get;组; }
public bool Archive {get;组; }
public bool Delflag {get;组; }
public decimal Coinsded {get;组; }
public decimal Outpoc {get;组; }
public System.DateTime Lastupd {get;组; }
public decimal Coins {get;组; }
public decimal Msp {get;组; }
}
在现实世界中，病人通过方式与一家保险公司联系的保险单。有一个 FK_PatientID ， FK_InsuranceCarrierID 和唯一ID， PK_PolicyNumber
我可能会让患者对象包含保险单对象的集合。以下这些行：
class Patient：Person
{
int PatientID {get;组; }
virtual IEnumerable& InsurancePolicy> InsurancePolicies {get;组; }
}
class InsurancePolicy
{
int PatientID {get;组; }
string PolicyNumber {get;组; }
string GroupNumber {get;组; }
bool IsActive {get;组; }
DateTime startDate {get;组; }
DateTime endDate {get;组; }
int InsuranceCarrierID {get;组; }
virtual Person Guarantor {get;组; } //所有担保人信息可通过人员聚合root访问，即：Guarantor.FirstName
string GuarantorRelation {get;组; }
string []优点{get;组; } //描述一组效益描述...自动解析EDI优点消息？单独的值对象类？可以包含copay / deduc / OoP
decimal可扣除{get;组; }
decimal Copay {get;组; }
decimal OutofPocket {get;组; }
virtual IEnumerable< Bill>关联银行{get;组; //所有与政策相关的帐单...也可以是InsuranceClaim对象...自动申请人Bill->声明？
}
还有一些其他事情需要在InsurancePolicy或其他地方像支付率百分比，但我现在要离开他们。
我的问题终于到达时寻找方法将数据映射到旧的FP表。具体看看担保人：作为一个 Person 对象，它将被存储在SQL模式的继承表中，所以什么是映射的最佳方式吗？简单的 .ToTable（Patins）在 InsurancePolicyMap 与（t => t .Guarantor.FirstName）.HasColumnName（Guarfname）似乎是合乎逻辑的，但是EF会自动处理单独的关系模式吗？可能更好的措辞：导航关系/继承之间的麻烦导致物理的 Person.FirstName ，SQLMap InsurancePolicy.Guarantor.FirstName ，VFPmap和物理 Patins.Guarfname ？
Billcnt ？幸运的是，无论什么原因，在App X中都没有实现，但是我认为的映射是什么？ AssociatedBills.Count（）？你会检查从FP表中提取价值的有效性吗？
由于你有自由统治，我会抓住机会升级整个系统。这是一个非常非常非常的数据模型，并且在任何时间内拖放这些遗产真的没有任何用处。这些不重复的重复编号的字段将是混乱和错误的连续来源。而实际上这是不可能的。实体框架有很多选项可以使数据模型与数据模型不同，但是这样做太多了。真的，在保留数据模型的同时重建应用程序是无用的。
你应该肯定规范化数据模型。使用外键 Accttype 等创建诸如 Insclass 的表格。还有一个 Benefit 表与FK到 InsurancePolicy ，因为您无法将字符串数组映射到数据库列。 / p>
但是首先（最重要的）要求清楚。完全自由地建立任何一种应用听起来好听，但用户总是记住一些事情。在你输入一行代码之前，我会花很多时间选择他们的大脑。同意先做的事情。然后在用例之后开始构建应用程序用例。并让他们测试每个用例。这将给他们时间适应新系统，慢慢脱离旧系统，随时随地微调要求。 （简而言之，这是敏捷开发）。
I am a fairly novice, enthusiast programmer. This is my first question, but I have been using stackoverflow for valuable information for several months now.
First, some context:
My current, somewhat jack-of-all trades job in an extremely small (<10 employees) specialist physician's office has put me in a unique position where I have free reign (and moderate resource backing) to develop and implement any kind of application/tool without any kind of demand or pressure as the current system functions well enough.
We currently run a fairly dated (circa ~2008) medical office management system that takes care of patient business accounts, billing, and insurance claim submissions. The office itself is fairly hap-hazardly networked. We have a couple of diagnostic machines that use standard DICOM, but the majority of medical records remains paper.
I may have bit off more than I can currently chew with my aspirations, but I have plans to slowly develop a more all-encompassing, domain-driven application that couples electronic medical records (evaluation/management and DICOM diagnostics) with the medical office management end. Once I have the framework of such an application laid, the sandbox aspect of my situation entices me in that I can explore and develop any kind of automation or tool I can dream.
Some honesty:
My experience is very limited in such things, but I am a very determined individual and I love application-driven learning.
My question:
I am working on first integrating with the current database of patient accounts.
Everything is currently stored in FoxPro 2.5 free tables. The relationships between the tables are not implicitly obtainable but quite a few are implied. Ideally I would like to have my new application with POCOs mapped with EF to an SQL database. That part is straightforward enough, but I would also like to have the same POCOs mapped to the current FoxPro 2.5 .dbfs (which won't necessarily have the same schema) in a separate DBContext.
Is such a thing possible as I am envisioning? I have been testing the waters of deriving all of the fluentAPI mappings (.ToTable(), .HasColumnName(), etc.) but it is a fairly daunting task and I would like some more experienced insight before dive head-first. I have been unable to find any relevant examples of anyone pulling off what I am attempting, which is somewhat discouraging as well.
Perhaps my approach is wrong. I am willing to adjust accordingly, but I like the idea of working with POCOs in my application and it is pretty important for my new application to be able to talk to the old database without implementing its unintuitive schema.
The key purpose of all the headache is to keep the current application fully functional while concurrently allowing me to run and develop my new application.
So in a nutshell:
Is it possible to use EF to integrate a new OOR/Domain-driven application with an old database of mildly different schema? If so, any tips or examples to get me started? If not, do I have any other functionally-similar alternatives? Thanks
Edit 1:
I'm going to refer to the currently used application as App X from now on.
App X's predecessor ran on Unix and sat on FoxPro/xBASE tables as well so App X was built on top of that presumably to trivialize customers upgrading. The App X directory also contains Visual Fox Pro 6 .dlls and an application file with a FoxPro .ico and the name "fbaseeng" which brings up a command prompt window titled "DTS Command Prompt". I don't know for sure how App X ticks, but 'DTS' stuck out to me and I spent some time seeing if there was a way I could use whatever data transformation they already implemented, but I eventually gave up.
The current database is a collection of 231 .dbf tables. Luckily, a good portion of them are seemingly either unused altogether, or only used in some round-about, temporary way where they don't store records outside the run-time of App X.
Several of the tables seem to be linking tables and another portion of them contain reference data like Type qualifier properties
public partial class Accttype
{
public decimal Acct_Type { get; set; }
public string Acct_Desc { get; set; }
public string Sb { get; set; }
public decimal Fee { get; set; }
public bool Acptasgn { get; set; }
public decimal Insclass1 { get; set; }
public decimal Insclass2 { get; set; }
public decimal Insclass3 { get; set; }
public decimal Insclass4 { get; set; }
public decimal Insclass5 { get; set; }
public string Acct_Grp { get; set; }
}
and static reference values like zip codes
public partial class Zip
{
public string Zipcode { get; set; }
public string City { get; set; }
public string St { get; set; }
public string Areacode { get; set; }
public decimal Ext_From { get; set; }
public decimal Ext_To { get; set; }
}
So far, I am most concerned with the following tables:
- Patdemo.dbf Contains records for all of the patients to visit the practice. It has around 100 columns and contains a plethora of information including name, address, insurance type(s), running account balance totals, etc. Straightforward primary key that is patient ID, but the format is '0.0'.
- Billing.dbf
Contains alpha-numeric ID-indexed bills associated with patients on a specific date of
service. Has about 80 columns including mostly foreign keys/type qualifiers and status
indicators (i.e. INS1_SENT). Has FK of patient ID
- Charges.dbf
Contains line items that fall under each Bill. It is either a table per inheritance or
a join of Charges and Postings/Payments as it contains both records indicated by C or
P
in a Type column. There doesn't seem to be a simple primary key, but Charges have
ChargeID and Postings/Payments have PostID, both with format BillID+"000N". To
throw a curveball, however, voiding adjustments have no ChargeID/PostID. Has FK
of BillID.
- Insur.dbf Contains insurance providers and information from address to electronic billing ID. Primary key is alpha-numeric ICode (example: BC01).
- Patins.dbf
Seems to be a linking table, but also contains information like the patient's insurance
ID number for the policy. Has FKs of patient ID and ICode.
There are various other referential tables I will want to keep concurrency with (like diagnoses, referring doctors, and CPT codes), but they are lower priority right now.
I haven't completely designed my new application's schema yet, but I do know it will be far more logical in that things like Addresses will be a concrete type regardless of if associated with a patient or insurance company.
For sake of this example, let's look at the pre-existing Patins.dbf POCO (it is one of the smallest tables):
public partial class Patins
{
public decimal Custid { get; set; }
public decimal Inskey { get; set; }
public string Insurcode { get; set; }
public string Insurnum { get; set; }
public string Groupnum { get; set; }
public string Guarlname { get; set; }
public string Guarfname { get; set; }
public string Guarmi { get; set; }
public string Guargen { get; set; }
public string Guaraddr { get; set; }
public string Guaraddr2 { get; set; }
public string Guarcity { get; set; }
public string Guarst { get; set; }
public string Guarzip { get; set; }
public string Guarcountr { get; set; }
public string Guarphone { get; set; }
public string Guaremail { get; set; }
public System.DateTime Guardob { get; set; }
public string Guarsex { get; set; }
public string Guaremp { get; set; }
public decimal Relation { get; set; }
public System.DateTime Startdate { get; set; }
public System.DateTime Enddate { get; set; }
public bool Active { get; set; }
public string Bcpc { get; set; }
public string Auth1 { get; set; }
public string Auth2 { get; set; }
public string Auth3 { get; set; }
public decimal Billcnt { get; set; }
public string Desc1 { get; set; }
public string Desc2 { get; set; }
public string Desc3 { get; set; }
public decimal Visits1 { get; set; }
public decimal Visits2 { get; set; }
public decimal Visits3 { get; set; }
public System.DateTime From1 { get; set; }
public System.DateTime From2 { get; set; }
public System.DateTime From3 { get; set; }
public System.DateTime To1 { get; set; }
public System.DateTime To2 { get; set; }
public System.DateTime To3 { get; set; }
public string Insnote { get; set; }
public string Char1 { get; set; }
public string Char2 { get; set; }
public string Char3 { get; set; }
public string Char4 { get; set; }
public string Char5 { get; set; }
public string Char6 { get; set; }
public string Char7 { get; set; }
public string Char8 { get; set; }
public string Char9 { get; set; }
public string Char10 { get; set; }
public System.DateTime Date1 { get; set; }
public System.DateTime Date2 { get; set; }
public decimal Num1 { get; set; }
public decimal Num2 { get; set; }
public string Createby { get; set; }
public System.DateTime Createdt { get; set; }
public string Modifyby { get; set; }
public System.DateTime Modifydt { get; set; }
public string Cobmemo { get; set; }
public System.DateTime Dinju { get; set; }
public System.DateTime Dsimbd { get; set; }
public System.DateTime Dsimed { get; set; }
public string Createtm { get; set; }
public string Modifytm { get; set; }
public bool Archive { get; set; }
public bool Delflag { get; set; }
public decimal Coinsded { get; set; }
public decimal Outpoc { get; set; }
public System.DateTime Lastupd { get; set; }
public decimal Coins { get; set; }
public decimal Msp { get; set; }
}
In the real world, a patient is associated with an insurance company by way of insurance policy. There is a FK_PatientID, FK_InsuranceCarrierID, and unique ID, PK_PolicyNumber (possibly PolicyNumber+InsuranceCarrierID to be safe?). Policies have benefits information that dictate payment, and spouses/families can share policies (usually appending -0n to policy number).
I will probably let Patient objects contain a collection of insurance policy objects. Along these lines:
class Patient : Person
{
int PatientID { get; set; }
virtual IEnumerable<InsurancePolicy> InsurancePolicies { get; set; }
}
class InsurancePolicy
{
int PatientID { get; set; }
string PolicyNumber { get; set; }
string GroupNumber { get; set; }
bool IsActive { get; set; }
DateTime startDate { get; set; }
DateTime endDate { get; set; }
int InsuranceCarrierID { get; set; }
virtual Person Guarantor { get; set; } //all guarantor information accessible via person aggregate root i.e: Guarantor.FirstName
string GuarantorRelation { get; set; }
string[] Benefits { get; set; } //delineated set of benefit descriptions... automatically parse from EDI benefits message?... seperate value object class?... could contain copay/deduc/OoP
decimal Deductible { get; set; }
decimal Copay { get; set; }
decimal OutofPocket { get; set; }
virtual IEnumerable<Bill> AssociatedBills { get; set; } //all bills associated with policy... could also be InsuranceClaim objects... Automapper Bill->Claim?
}
There are a few other things that need to be represented either in InsurancePolicy or elsewhere like pay rate percentage, but I am leaving them off for now.
My question finally arrives when looking for ways to map the data to/from the old FP Tables. Specifically looking at Guarantor: as a Person object, it will be stored in an inheritance table in the SQL schema so what is the best way to go about mapping? Simply .ToTable("Patins") in the InsurancePolicyMap with (t => t.Guarantor.FirstName).HasColumnName("Guarfname") seems logical, but does EF take care of the separate relationship patterns automatically? Possibly better worded: Does it have trouble navigating the relationship/inheritance that lies between the physical Person.FirstName, the SQLMap, InsurancePolicy.Guarantor.FirstName, the VFPmap, and the physical Patins.Guarfname?
What about Billcnt? It is luckily not implemented in App X for whatever reason, but what would the mapping be for what I assume would be AssociatedBills.Count()? Would you just check validity pulling the value from the FP Table?
Since you have "free reign" I would take the chance to upgrade the whole system. This is a very very poor data model and it's really no use to drag this legacy along any time longer. These nondescript repetitive numbered fields will be a continuous source of confusion and bugs. And it will be virtually impossible to build a decent domain from it. Entity Framework has plenty of options to shape the class model differently that the data model, but this would be too much. Really, it's useless to rebuild the application while keeping the data model.
You should definitely normalize the data model. Create tables like Insclass with a foreign key to Accttype and so on and so forth. Also a Benefit table with an FK to InsurancePolicy as you can't map a string array to a database column.
But first (and foremost) get the requirements clear. It sounds good to have total liberty to build "any kind of application", but users always have something in mind. I would take ample time to pick their brains before you even type one line of code. Agree upon the things to do first. Then start building the application use case after use case. And let them test each use case. This will give them time to get used to the new system and slowly detach from the old system, and to fine-tune requirements as you go. (This is agile development in a nutshell).
这篇关于复杂（多模式）实体框架对象映射，用于办公室管理应用程序的无缝集成（和最终替换）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
