How to Call Stored Procedures with Parameters in Entity Framework 6 Code-First?

Entity Framework 6 Code-First: Efficiently Calling Stored Procedures with Parameters

In Entity Framework 6's code-first approach, leveraging stored procedures is essential for complex database interactions. A common challenge involves effectively calling stored procedures and managing parameter passing.

Let's illustrate with a stored procedure designed to insert a department:

<code class="language-sql">ALTER PROCEDURE [dbo].[insert_department]
    @Name [varchar](100)
AS
BEGIN
    INSERT [dbo].[Departments]([Name])
    VALUES (@Name)

    DECLARE @DeptId int

    SELECT @DeptId = [DeptId]
    FROM [dbo].[Departments]
    WHERE @@ROWCOUNT > 0 AND [DeptId] = SCOPE_IDENTITY()

    SELECT t0.[DeptId]
    FROM [dbo].[Departments] AS t0
    WHERE @@ROWCOUNT > 0 AND t0.[DeptId] = @DeptId
END</code>

To integrate this stored procedure into your Entity Framework context, map it within your model:

<code class="language-csharp">modelBuilder
    .Entity<department>()
    .MapToStoredProcedures(s =>
    {
        s.Update(u => u.HasName("modify_department")
                      .Parameter(b => b.Department, "department_id")
                      .Parameter(b => b.Name, "department_name"))
          .Delete(d => d.HasName("delete_department")
                      .Parameter(b => b.DepartmentId, "department_id"))
          .Insert(i => i.HasName("insert_department")
                      .Parameter(b => b.Name, "department_name"));
    });</code>

This mapping allows you to call the stored procedure directly from your DbContext:

<code class="language-csharp">this.Database.SqlQuery<department>("insert_department", new SqlParameter("@Name", departmentName));</code>

For stored procedures returning multiple result sets (as shown in the example), consult Microsoft's documentation on "Stored Procedures with Multiple Result Sets" for best practices in handling the returned data. This will provide guidance on efficiently processing the multiple datasets.

The above is the detailed content of How to Call Stored Procedures with Parameters in Entity Framework 6 Code-First?. For more information, please follow other related articles on the PHP Chinese website!