Entity Framework：使用存储过程返回原始表结果

Question

在EF中，是否可以使用存储过程返回原始的DataTable/DataSet，而不是返回映射/翻译后的实体对象？

- Sisyphus

Answer 1

EF建立在ADO.NET之上，因此每当您需要时，可以直接访问DbContext的DbConnection并直接使用它。例如：

        using (var db = new MyDbContext())
        {
            db.Database.Connection.Open();
            var con = (SqlConnection)db.Database.Connection;
            var cmd = new SqlCommand("exec MyProc", con);
            DataTable dt = new DataTable();
            using (var rdr = cmd.ExecuteReader())
            {
                dt.Load(rdr);
            }
            //. . .

- David Browne - Microsoft

Answer 2

是的，这是可能的。

下面我描述了如何实现。存储过程是数据库的一部分。因此，最好将存储过程添加到访问数据库并创建数据库模型的类中：您的dbContext。

public class MyDbContext : DbContext
{
    // TODO: add DbSet properties

    #region stored procedures
    // TODO (1): add a function that calls the stored procedure
    // TODO (2): add a function to check if the stored procedure exists
    // TODO (3): add a function that creates the stored procedure
    // TODO (4): make sure the stored procedure is created when the database is created
    #endregion stored procedure
}

TODO (1): 调用存储过程的过程：

private const string MyStoredProcedureName = ...;
private const string myParamName1 = ...;
private const string myParamName2 = ...;

public void CallMyStoredProcedure(MyParameters parameters)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(myParamName1, parameters.GetParam1Value(),
        new SqlParameter(myParamName2, parameters.GetParam2Value(),
        ... // if needed add more parameters
    };
    const string sqlCommand = @"Exec " + MyStoredProcedureName
        + "  @" + myParamName1
        + ", @" + myParamName2
        ... // if needed add more parameters
        ;
    this.Database.ExecutSqlCommand(sqlComman, functionParameters);
}

TODO (2) 检查存储过程是否存在

// returns true if MyStoredProcedure exists
public bool MyStoredProcedureExists()
{
    return this.ProcedureExists(MyStoredProcedureName);
}

// returns true if stored procedure with procedureName exists
public bool StoredProcedureExists(string procedureName)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"procedurename", procedureName),
    };
    string query = @"select [name] from sys.procedures where name= @procedurename";
    return this.Database.SqlQuery<string>(query, functionParameters)
        .AsEnumerable()                       // bring to local memory
        .Where(item => item == procedureName) // take only the procedures with desired name
        .Any();                               // true if there is such a procedure
}

TODO (3) 创建存储过程：

public void CreateProcedureUpdateUsageCosts(bool forceRecreate)
{
    bool storedProcedureExists = this.MyStoredProcedureExists();

    // only create (or update) if not exists or if forceRecreate:
    if (!storedProcedureExists || forceRecreate)
    {  // create or alter:
        Debug.WriteLine("Create stored procedures");

        // use a StringBuilder to create the SQL command that will create the
        // stored procedure
        var x = new StringBuilder();

        // ALTER or CREATE?
        if (!storedProcedureExists)
        {
            x.Append(@"CREATE");
        }
        else
        {
            x.Append(@"ALTER");
        }

        // procedure name:
        x.Append(@" procedure ");
        x.AppendLine(MyStoredProcedureName);

        // parameters: (only as an example)
        x.AppendLine(@"@ReportPeriod int,");
        x.AppendLine(@"@CustomerContractId bigint,");
        x.AppendLine(@"@CallType nvarChar(80),");
        // etc.

        // code
        x.AppendLine(@"as");
        x.AppendLine(@"begin");
        // only as example some of my procedure
        x.AppendLine(@"Merge [usagecosts]");
        x.AppendLine(@"Using (Select @ReportPeriod as reportperiod,");
        x.AppendLine(@"              @CustomerContractId as customercontractId,");
        ...
        x.AppendLine(@"end");

        // execute the created SQL command
        this.Database.ExecuteSqlCommand(x.ToString());
    }
}

TODO (4) 确保在创建数据库时创建存储过程

在MyDbContext中：

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // TODO: if needed add fluent Api to build model

    // create stored procedure
    this.CreateProcedureUpdateUsageCosts(true);
}

使用方法：

using (var dbContext = new MyDbContext(...))
{
   MyParameters parms = FillMyParameters();
   dbContext.CallMyStoredProcedure(parms);
}

- Harald Coppoolse

---

对于初学者来说，这可能看起来有点过度设计，但为了减少时间和复杂性，这将是最佳方法。还有一件事我想从你那里了解，是否有任何替代方法来创建第三步。实际上，我不想像追加字符串那样做。谢谢您的分享。 - adnan umar

如果您不想创建字符串，总是可以将它们写成两个字符串，一个用于Create，另一个用于Alter: const string SqlCreateText = "Create Procedure " + myStoredProcedureName + "... etc, "; - Harald Coppoolse

Answer 3

请查看：https://dev59.com/R2855IYBdhLWcg3w3Ifs#73263659。

您只需要用您的存储过程替换 SQL 查询即可。

- Mimina