Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored Procedures in EF Core 3.0

How to use stored procedures in EF Core 3.0 ?

I have tried the following

var user = await _context.Query<User>().FromSql("EXECUTE dbo.spGeneral_Authenticate").FirstOrDefaultAsync();

var user = await _context.Query<User>().FromSqlRaw("EXECUTE dbo.spGeneral_Authenticate").FirstOrDefaultAsync();

var user = await _context.Set<User>().FromSql("EXECUTE dbo.spGeneral_Authenticate").FirstOrDefaultAsync();

var user = await _context.Set<User>().FromSqlRaw("EXECUTE dbo.spGeneral_Authenticate").FirstOrDefaultAsync();

EF core translating the SQL in wrong way. I got the translated SQL from log file.

2019-09-27 11:21:36.086 +05:30 [Error] Failed executing DbCommand ("30"ms) [Parameters=[""], CommandType='Text', CommandTimeout='30']" ""SELECT TOP(1) [u].[FullName], [u].[Password], [u].[UserName] FROM ( EXECUTE dbo.spGeneral_Authenticate ) AS [u]" 2019-09-27 11:21:36.154 +05:30 [Error] An exception occurred while iterating over the results of a query for context type '"__________Context"'." ""Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'EXECUTE'. Incorrect syntax near ')'.

Translated SQL:

SELECT TOP(1) [u].[FullName], [u].[Password], [u].[UserName]
FROM (
    EXECUTE dbo.spGeneral_Authenticate
) AS [u]
like image 222
Palanikumar Avatar asked Sep 27 '19 06:09

Palanikumar


People also ask

How can use stored procedure in .NET core in Entity Framework?

First, create your existing SQL server database named "db_core_sp_call" which will be utilized in ASP.NET Core web application by executing the following SQL script; i.e.: USE [db_core_sp_call] GO. /****** Object: StoredProcedure [dbo].

Can we use stored procedure in Entity Framework?

You can use stored procedures either to get the data or to add/update/delete the records for one or multiple database tables. EF API creates a function instead of an entity in EDM for each stored procedure and User-Defined Function (UDF) in the target database.

Does EF core support stored procedures?

EF Core already supports querying data from stored procedures. This feature will allow mapping the inserts, updates, and deletes generated by SaveChanges to stored procedures in the database.


2 Answers

Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'EXECUTE'. Incorrect syntax near ')'.

For the above error, we should use .ToList() or .ToListAsync() not .FirstOrDefault() or .FirstOrDefaultAsync()

It will work

var user = await _context.Set<User>().FromSql("EXECUTE dbo.spTest").ToListAsync();

It won't work

var user = await _context.Set<User>().FromSql("EXECUTE dbo.spTest").FirstOrDefaultAsync();
/*
Transalated SQL:
SELECT TOP(1) [u].[FullName], [u].[Password], [u].[UserName]
FROM (
    EXECUTE dbo.spTest
) AS [u]
*/
like image 70
Palanikumar Avatar answered Oct 16 '22 11:10

Palanikumar


The accepted answer nails it. Here are my two cents however:

Also, if you want to get only one result and still want to make the server call asynchronously:

var user = (await _context.Set<User>().FromSql("EXECUTE dbo.spTest").ToListAsync()).FirstOrDefault();
like image 29
Leo Avatar answered Oct 16 '22 10:10

Leo