如何在实体框架中使用外部联接将多个表映射到单个实体？
我有两个表：
发票（ID、InternalPrice、ExternalPrice）
InvoiceSummary (InvoiceID, Variance)
在此应用程序中，我们有一个遍历发票表的流程，在实体框架中创建对象表示。然后，应用程序必须计算 (InternalPrice – externalPrice) 之间的差值并将其存储在 Variance 列中。
为了允许实体框架在大量行（约 500 万行）上有效地执行此操作，我将计算结果分离到一个单独的表中。这允许我使用对象表示来计算方差（加上其他业务逻辑），然后使用“SQL 批量插入”将数据快速插入到我的数据库中（比正常的实体更新快得多）。
我想合并发票和发票InvoiceSummary 表合并到单个实体对象中，同时保留单独的表以供 BCP 纵容，如上所述。但是，InvoiceSummary 中可能并不总是有匹配的行。
看起来实体框架需要两个表之间进行 1 对 1 映射才能实现合并实体。 有谁知道如何让实体框架用 0 到 1 映射来表示这一点？
如果我找不到实体框架解决方案，我可以使用视图来“隐藏”底层表结构。我正在使用 SQL Server，它支持可更新视图，所以这对 EF 应该是透明的？替代结构解决方案也受到欢迎。
I have two tables:
Invoice (ID, InternalPrice, ExternalPrice)
InvoiceSummary (InvoiceID, Variance )
In this application we have a process that goes through the Invoice table, creating object representations in Entity Framework. The application must then compute the difference between the (InternalPrice – ExternalPrice) and store it in the Variance column.
To allow entity framework to perform this efficiently on large numbers of rows (~ 5 million) I have separated the compute result into an individual table. This allows me to use object representations to compute the variance (plus other business logic), and then use 'SQL bulk insert' to insert the data into my database quickly (much faster than a normal Entity update).
I’d like to merge the Invoice & InvoiceSummary tables into a single entity object, while keeping separate tables for the BCP connivance as described above. However there may not always be a matching row in InvoiceSummary.
It looks like Entity Framework requires a 1 to 1 mapping between the two tables to implement a merged entity. Does anyone know how I can get Entity Framework to represent this with a 0 to 1 mapping?
If I don’t find an entity framework solution I could use a view to ‘hide’ the underlying table structure. I'm using SQL server, which supports updatable views, so this should be transparent to EF? Alternate structures solutions are welcome as well.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的结论是正确的，即实体拆分（将单个实体拆分到多个表中）需要表之间存在 1:1 关系。如果您有能力向数据库添加视图，我认为您最好采用该路线，然后将该视图映射到单个实体。这个提议的解决方案有一个很大的警告。除非您可以在数据库中创建可更新视图，否则您将获得只读视图，并且只能使用 EDMX（设计器）映射到只读视图。 Code First 不支持映射到只读视图。
还有其他一些选项：
数据库中的过程。可以映射到 EDMX 中的函数
或首先通过代码中的函数执行。
认为将实体映射到视图是正确的方法。
只读就可以了，可以使用dbcontext.database.executecommand。
hth（希望我的一切都是正确的，因为它来自记忆……我不相信……这就是为什么我把这些东西写在书里！）
you are correct in your conclusion that entity splitting (split a single entity across multiple tables) requires a 1:1 relationship between the tables. If you have the ability to add a view to the database, I think you are best going that route and then mapping that view to a single entity. There's a big caveat with this proposed solution. Unless you can create an updatable view in the database, you will get a read-only view and you can only map to read-only views using EDMX (the designer). Code First does not support mapping to read-only views.
There are a few other options:
procedure in teh database. That can be mapped to a function in EDMX
or executed via a function in code first.
think map an entity to a view is the way to go.
read-only is okay, you can use dbcontext.database.executecommand.
hth (and hope I've got that all correct since it was from memory...which I don't trust...which is why I write this stuff down in books!)