CODEWITHSUNDEEP
c#stored-proceduresentity-framework-coreef-core-2.0
Hafizullah Mahmudi
Hafizullah Mahmudi

Reputation: 61

Cannot run stored procedure from Entity Framework Core

I have the following code in EF Core to run a stored procedure:

TenandId = user.TenantId;
userName = user.UserName;
updateDate = DateTime.Now;
TimeStart = 139601;
TimeEnd = 139612;
RequestId = id;

_context.TempRequest.FromSql("EXEC dbo.AddRequest_To_TempRequest @TimeStart, @TimeEnd, @TenantId, @UpdateDate, @UserName, @RequestId",
                new SqlParameter("@TimeStart", TimeStart),
                new SqlParameter("@TimeEnd", TimeEnd),
                new SqlParameter("@TenantId", TenandId),
                new SqlParameter("@UpdateDate", updateDate),
                new SqlParameter("@UserName", userName),
                new SqlParameter("@RequestId", RequestId));

but when I run it, it is not working, but it is also not throwing any errors.

Am I making any mistake?

Thank you

Upvotes: 1

Views: 656

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205529

FromSql is used when the raw SQL represents a query. And the method creates LINQ / EF Core query, but as usual with IQueryable<> / IEnumerable<> does not execute it until it is iterated (with foreach, ToList() etc.) - the so called deferred execution. This should explain why "it is not working, but it is also not throwing any errors".

For non query raw SQL statements EF Core provides another method ExecuteSqlCommand (or ExecuteSqlRaw / ExecuteSqlInterpolated in EF Core 3.0+) which executes immediately.

Both methods can be used to call stored procedure when executed. Looks like the later is more appropriate for your scenario, so replacing the

_context.TempRequest.FromSql

with

_context.Database.ExecuteSqlCommand

should do the job.

Upvotes: 1

Related Questions