C# 如何使用 EntityFramework 调用存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14264750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call Stored Procedures with EntityFramework?
提问by Ivy
I have generated an EF4 Model from a MySQL database and I have included both StoredProcedures and Tables.
我从 MySQL 数据库生成了一个 EF4 模型，并且包含了 StoredProcedures 和 Tables。
I know how to make regular instert/update/fetch/delete operations against the EF but I can't find my StoredProcedures.
我知道如何对 EF 进行常规的插入/更新/获取/删除操作，但我找不到我的 StoredProcedures。
This was what I was hoping for:
这是我所希望的：
using (Entities context = new Entities())
{
context.MyStoreadProcedure(Parameters);
}
Edit 1:
编辑1：
This is how it looked without EF:
这是没有 EF 时的样子：
sqlStr = "CALL updateGame(?,?,?,?,?,?,?)";
commandObj = new OdbcCommand(sqlStr, mainConnection);
commandObj.Parameters.Add("@id,", OdbcType.Int).Value = inGame.id;
commandObj.Parameters.Add("@name", OdbcType.VarChar, 255).Value = inGame.name;
commandObj.Parameters.Add("@description", OdbcType.Text).Value = ""; //inGame.description;
commandObj.Parameters.Add("@yearPublished", OdbcType.DateTime).Value = inGame.yearPublished;
commandObj.Parameters.Add("@minPlayers", OdbcType.Int).Value = inGame.minPlayers;
commandObj.Parameters.Add("@maxPlayers", OdbcType.Int).Value = inGame.maxPlayers;
commandObj.Parameters.Add("@playingTime", OdbcType.VarChar, 127).Value = inGame.playingTime;
return Convert.ToInt32(executeScaler(commandObj));
PS. I can change EF version if needed
附注。如果需要，我可以更改 EF 版本
Edit 1:
编辑1：
CREATE DEFINER=`106228`@`%` PROCEDURE `updateGame`(
inId INT,
inName VARCHAR(255),
inDescription TEXT,
inYearPublished DATETIME,
inMinPlayers INT,
inMaxPlayers INT,
inPlayingTime VARCHAR(127)
)
回答by tam tam
This is what I recently did for my Data Visualization Application which has a 2008 SQL Database. In this example I am recieving a list returned from a stored procedure:
这是我最近为具有 2008 SQL 数据库的数据可视化应用程序所做的。在这个例子中，我收到一个从存储过程返回的列表：
public List<CumulativeInstrumentsDataRow> GetCumulativeInstrumentLogs(RunLogFilter filter)
{
EFDbContext db = new EFDbContext();
if (filter.SystemFullName == string.Empty)
{
filter.SystemFullName = null;
}
if (filter.Reconciled == null)
{
filter.Reconciled = 1;
}
string sql = GetRunLogFilterSQLString("[dbo].[rm_sp_GetCumulativeInstrumentLogs]", filter);
return db.Database.SqlQuery<CumulativeInstrumentsDataRow>(sql).ToList();
}
And then this extension method for some formatting in my case:
然后在我的情况下，此扩展方法用于某些格式：
public string GetRunLogFilterSQLString(string procedureName, RunLogFilter filter)
{
return string.Format("EXEC {0} {1},{2}, {3}, {4}", procedureName, filter.SystemFullName == null ? "null" : "\'" + filter.SystemFullName + "\'", filter.MinimumDate == null ? "null" : "\'" + filter.MinimumDate.Value + "\'", filter.MaximumDate == null ? "null" : "\'" + filter.MaximumDate.Value + "\'", +filter.Reconciled == null ? "null" : "\'" + filter.Reconciled + "\'");
}
回答by Quinton Bernhardt
One way is to use the Database property off the DbContext:
一种方法是使用 DbContext 之外的 Database 属性：
SqlParameter param1 = new SqlParameter("@firstName", "Frank");
SqlParameter param2 = new SqlParameter("@lastName", "Borland");
context.Database.ExecuteSqlCommand("sp_MyStoredProc @firstName, @lastName",
param1, param2);
EF5 definitely supports that.
EF5 绝对支持这一点。
回答by ESG
Once your stored procedure is imported in your model, you can right click in it (from the model browser, in the Context.Store/Stored Proceduressection), and click Add Function Import. If you need a complex type as a result, you can create it right there.
在您的模型中导入存储过程后，您可以右键单击它（从模型浏览器中的Context.Store/Stored Procedures部分），然后单击Add Function Import。如果您因此需要一个复杂类型，您可以在那里创建它。
回答by user2997069
You have use the SqlQuery function and indicate the entity to mapping the result.
您已使用 SqlQuery 函数并指示要映射结果的实体。
I send an example as to perform this:
我发送一个示例来执行此操作：
var oficio= new SqlParameter
{
ParameterName = "pOficio",
Value = "0001"
};
using (var dc = new PCMContext())
{
return dc.Database
.SqlQuery<ProyectoReporte>("exec SP_GET_REPORTE @pOficio",
oficio)
.ToList();
}
回答by Dave
Basically you just have to map the procedure to the entity using Stored Procedure Mapping.
基本上，您只需使用存储过程映射将过程映射到实体。
Once mapped, you use the regular method for adding an item in EF, and it will use your stored procedure instead.
映射后，您可以使用常规方法在 EF 中添加项目，而它将使用您的存储过程。
Please see: This Linkfor a walkthrough. The result will be adding an entity like so (which will actually use your stored procedure)
请参阅：此链接以进行演练。结果将添加一个像这样的实体（它实际上将使用您的存储过程）
using (var ctx = new SchoolDBEntities())
{
Student stud = new Student();
stud.StudentName = "New sp student";
stud.StandardId = 262;
ctx.Students.Add(stud);
ctx.SaveChanges();
}
回答by Dib
Based up the OP's original request to be able to called a stored proc like this...
基于 OP 的原始请求，以便能够像这样调用存储过程......
using (Entities context = new Entities())
{
context.MyStoreadProcedure(Parameters);
}
Mindless passengerhas a project that allows you to call a stored proc from entity frame work like this....
Mindless 乘客有一个项目，它允许您像这样从实体框架中调用存储过程......
using (testentities te = new testentities())
{
//-------------------------------------------------------------
// Simple stored proc
//-------------------------------------------------------------
var parms1 = new testone() { inparm = "abcd" };
var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
var r1 = results1.ToList<TestOneResultSet>();
}
... and I am working on a stored procedure framework(here) which you can call like in one of my test methods shown below...
...我正在研究一个存储过程框架（这里），你可以像下面显示的我的测试方法之一一样调用它......
[TestClass]
public class TenantDataBasedTests : BaseIntegrationTest
{
[TestMethod]
public void GetTenantForName_ReturnsOneRecord()
{
// ARRANGE
const int expectedCount = 1;
const string expectedName = "Me";
// Build the paraemeters object
var parameters = new GetTenantForTenantNameParameters
{
TenantName = expectedName
};
// get an instance of the stored procedure passing the parameters
var procedure = new GetTenantForTenantNameProcedure(parameters);
// Initialise the procedure name and schema from procedure attributes
procedure.InitializeFromAttributes();
// Add some tenants to context so we have something for the procedure to return!
AddTenentsToContext(Context);
// ACT
// Get the results by calling the stored procedure from the context extention method
var results = Context.ExecuteStoredProcedure(procedure);
// ASSERT
Assert.AreEqual(expectedCount, results.Count);
}
}
internal class GetTenantForTenantNameParameters
{
[Name("TenantName")]
[Size(100)]
[ParameterDbType(SqlDbType.VarChar)]
public string TenantName { get; set; }
}
[Schema("app")]
[Name("Tenant_GetForTenantName")]
internal class GetTenantForTenantNameProcedure
: StoredProcedureBase<TenantResultRow, GetTenantForTenantNameParameters>
{
public GetTenantForTenantNameProcedure(
GetTenantForTenantNameParameters parameters)
: base(parameters)
{
}
}
If either of those two approaches are any good?
如果这两种方法中的任何一种都有好处？
回答by Hooman Bahreini
This is an example of querying MySQL procedure using Entity Framework
这是使用实体框架查询 MySQL 过程的示例
This is the definition of my Stored Procedure in MySQL:
这是我在 MySQL 中的存储过程的定义：
CREATE PROCEDURE GetUpdatedAds (
IN curChangeTracker BIGINT
IN PageSize INT
)
BEGIN
-- select some recored...
END;
And this is how I query it using Entity Framework:
这就是我使用实体框架查询它的方式：
var curChangeTracker = new SqlParameter("@curChangeTracker", MySqlDbType.Int64).Value = 0;
var pageSize = new SqlParameter("@PageSize", (MySqlDbType.Int64)).Value = 100;
var res = _context.Database.SqlQuery<MyEntityType>($"call GetUpdatedAds({curChangeTracker}, {pageSize})");
Note that I am using C# String Interpolationto build my Query String.
请注意，我使用C# 字符串插值来构建我的查询字符串。
