如何使用EntityFramework调用存储过程？

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进行常规instert /更新/获取/删除操作，但我找不到我的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

PS。如果需要，我可以更改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)
)

7 个解决方案

#1

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肯定支持这一点。

#2

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();
}

#3

Once your stored procedure is imported in your model, you can right click in it (from the model browser, in the Context.Store/Stored Procedures section), 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。如果您需要复杂类型，可以在那里创建它。

#4

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 Link for 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();
        }

#5

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 passenger has 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?

如果这两种方法中的任何一种都有好处吗？

#6

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 + "\'");

        }

#7

This is an example of querying MySQL procedure using Entity Framework

这是使用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:

这就是我使用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 Interpolation to build my Query String.

请注意，我使用C＃String Interpolation来构建我的查询字符串。