实体框架6混合存储过程和SQL查询生成 [英] Entity Framework 6 Mixed Stored Procedure and SQL Query Generation

问题描述

阅读CodePlex上的EF文档我了解目前不可能使用存储过程和SQL查询生成的混合，用于持久的域模型更改。

Reading the EF documentation on CodePlex I understand it's not currently possible to use both a mixture of stored procedure and SQL query generation for persisting domain model changes.

然而，有很多原因我觉得这是令人沮丧的，它不允许我使用两者的混合，当它的前身 - Linq 2 SQL允许我这样做。

However, there are numerous reasons why I find this frustrating that it doesn't allow me to use a mixture of both when its predecessor - Linq 2 SQL, allowed me to do this.

我有一个复杂的将我的域模型持久化到数据库中的多个表，从而我想使用存储过程进行插入。我不想使用存储过程进行更新的原因是没有一个干净的方法来仅在受影响的列上运行更新 - 将所有值解析为更新sproc，我将必须更新所有的字段记录（除非我再次选择所有旧值进行比较，或者在UPDATE语句谓词子句中包含这些字段 - 这两种方法都是混乱的）。这是一个问题，因为触发器附加到表中，其中检查各个字段的更新，这更多是遗留系统问题 - 所以我们不会走下去使用触发器作为一个好设计的主观事项。

I have a complicated scenario for persisting my domain model to multiple tables in the database, whereby I want to use a stored procedure for insertion. The reason why I don't want to use a stored procedure for updates is that there isn't a clean way to run the update on only those columns affected - parsing all values to the update sproc I would have to update all fields for that record (unless I select all old values again for comparison or include the fields in the UPDATE statement predicate clause - kind of messy both ways). This is a problem due to triggers being attached to the table in which individual fields are checked for updates, this is more of a legacy system issue - so we won't go down the road of the subjective matter of using triggers being a good design.

将所有字段传递给更新sproc即使没有更改也是不必要的，可以对网络流量做出贡献，而EF知道使用SQL时要显式更新的字段查询生成模式。

Passing all fields to the update sproc even if they haven't changed is unnecessary and can contribute to network traffic, whereas EF knows which fields to explicitly update when using SQL query generation mode.

是否有任何实体框架加载项允许我使用存储过程映射的混合来插入，然后用于查询模型的SQL查询生成持久性？

Are there any entity framework add-ons which allow me to use a mixture of stored procedure mapping for inserts, and then SQL query generation for updates for model persistency?

对于我的数据库，我专门使用SQL Server，但我相信一个合适的加载项不会耦合到我选择的数据存储。

For my database I am specifically using SQL Server, however I believe a suitable add-on wouldn't be coupled to my chosen data store.

推荐答案

我也在寻找混合物。
DbSet 有一个名为 SqlQuery 的方法，您可以在其中放置自定义 SELECT 查询（带有一些警告）。您也可以调用Store Procedures。
看看这个： https://msdn.microsoft.com /en-us/data/jj592907.aspx 显示此例子：

I was looking for that mixture too. DbSet has a method called SqlQuery where you can place your custom SELECT queries (with some warnings). You can call Store Procedures too. Take a look at this: https://msdn.microsoft.com/en-us/data/jj592907.aspx where this exmaple is shown:

using (var context = new BloggingContext()) 
{ 
    var blogs = context.Blogs.SqlQuery("dbo.GetBlogs").ToList(); 
}

我以这种方式为自己使用：

I just used it for myself in this way:

db.RegistroSet.SqlQuery("DELETE FROM RegistroSET; SELECT * FROM RegistroSet").ToList();
int c = db.RegistroSet.Count();

...因为我其实想要清除我的200.000行表。这是快速的，上下文是确定的。但是，我必须包含选择以确定上下文的完整性。即使我没有得到结果，调用 ToList（）也是很重要的。

...because I actually wanted to clear my 200.000 rows table. It was fast and the context is ok. But I had to include the Select for manteining context's integrity. The call for ToList() is important even if I don't get the result.

这篇关于实体框架6混合存储过程和SQL查询生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！