Efcore: Couldn't use stored procedures in EF Core 3.0

[palanikumar32]

I have tried the following, the below methods are working fine in EF Core 2.2.6 but not in EF Core 3.0

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]

Steps to reproduce

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

Further technical details

EF Core version: 3.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.3

[nelero]

Hi,
so what is the solution ? Facing the same problem

[roji]

Check out this breaking change note which explains what's happening and provides the solution.

[palanikumar32]

Hi,
so what is the solution ? Facing the same problem

https://stackoverflow.com/a/58167478/1019435

[roji]

As the note linked above says, the solution is to explicitly switch to client evaluation by inserting AsEnumerable before composing over the stored procedure:

c# context.Products.FromSqlRaw("[dbo].[Ten Most Expensive Products]").AsEnumerable().FirstOrDefault();

This produces the same behavior as in pre-3.0 versions of EF Core, but you have to be explicit about it.

[max-favilli]

the solution is to explicitly switch to client evaluation by inserting AsEnumerable before composing over the stored procedure:

Frankly I would like to ask people in the EF Core tema if they realize what it means when you have thousands of those calls to edit in application.

I see the benefit in removing client implicit evaluation, but god, what about providing some tool to refactor the code.

Sometimes I wonder how many really use EF in production, in large complex applications.