Calling a simple stored procedure in EF core 3.1

Question

I have a very simple stored procedure:

CREATE PROCEDURE [dbo].[ClearIterations]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    delete from iterations
END
GO

When calling it from EF it is not called. I get no errors:

public void ClearIterations()
{
    this.Iterations.FromSqlRaw("ClearIterations").IgnoreQueryFilters();
}

Any pointers? I found the sample above on another thread in here that where the code above is the answer. It seems kind of strange I have to call this this.Iterations to call a SP.

[Database.ExecuteSqlRaw](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlraw?view=efcore-3.1) — Ivan Stoev, Apr 21 '20 at 20:31
For simplest and most complete answer, check this out: https://stackoverflow.com/a/75465142/8644294 — Ash K, Feb 15 '23 at 20:34

score 8 · Accepted Answer · answered Apr 22 '20 at 07:50

8

EF Core 3.x+ provides two raw SQL sets of methods - FromSql and ExecuteSql, both with Raw / Interpolated and Async versions.

The former are used for querying. They return IQueryable<T>, allow query composition and as any LINQ query are not executed until the result is enumerated.

While the later is used to immediately execute arbitrary SQL (DDL, DML, batch etc.). They are EF Core equivalent of ADO.NET ExecuteNonQuery and return the records affected. Output (or input/output) primitive value parameters can be used to obtain the results.

Shortly, ExecuteSql methods are what you are seeking for. With your example, ExecuteSqlRaw, e.g. (assuming this is method in your DbContext derived class):

public void ClearIterations()
{
    this.Database.ExecuteSqlRaw("ClearIterations");
}

answered Apr 22 '20 at 07:50

Ivan Stoev

195,425
15
312
343

1

How do I get the result from `Select GetDate()` with `ExecuteSqlRaw`? – Old Geezer May 06 '20 at 11:22
@OldGeezer The same way you would in ADO.NET `DbCommand.ExecuteNonQuery` - using parameters. Of course you would need something like "select $someOutParam = GetDate()" rather than just "select". If you really want to execute query, then `FromSql` is the only way with all associated drawbacks. – Ivan Stoev May 06 '20 at 12:15
Thanks. I did it using https://stackoverflow.com/a/45262161/936293. Surprised that it requires so many lines of code just for a one-line statement. – Old Geezer May 06 '20 at 12:20
@OldGeezer It's not surprising because that's actually the old good ADO.NET way. The only EF Core code is `Database.GetDbConnection()` which gives you the configured `DbConnection` object which EF Core is using internally, but nothing more than that. – Ivan Stoev May 06 '20 at 12:24

Yannick · Answer 2 · 2021-01-06T06:53:07.560

//Interface
public interface IRepository
{
    Task AddQualification(int userId, string qualification);
}

Repository class implementing your interface remember to register your interface in your startup class ConfigureServices services.AddScoped<IRepository, Repository>()

public class Repository : IRepository
{
    public async Task AddQualification(int userId, string qualification)
    {
        await appDbContext.Database.ExecuteSqlRawAsync("InsertQualification {0}, {1}", userId, qualification);
    }
}

Constructor injection of the interface, then call the method

public async Task OnPost()
{
    await _repository.AddQualification(1, "English A-Level");
}

SQL Stored Procedure

CREATE PROCEDURE InsertQualification 
    -- Add the parameters for the stored procedure here
    @UserID int, 
    @Qualification varchar(255)

AS
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dbo.UserQualifications (UserID, Qualification)
    VALUES (@UserID, @Qualification)

END
GO

Please post you code as **text** - formatted as a code block (surround with two rows each of three backticks: ```). — Adrian Mole, Jan 05 '21 at 21:08

Calling a simple stored procedure in EF core 3.1

2 Answers2

Linked

Related