EFCore 3.1 FromSqlRaw is not working and throwing a bizarre error message

user_7897881 0
Viewed 4161

I have the most bizarre issue with EF Core 3.1. In EF Core 2.2 I used to be able to execute stored procedures. I see there is a breaking change in the documentation but, I am following the documentation exactly and it is not working. I have no nulls anywhere in the returned data. The NoticeOfInspection object matches the returned data exactly. What on Earth did they change that this is not working?

var data = _dbContext.NoticeOfInspections.FromSqlRaw("EXEC dbo.NewReportApp_NoticeOfInspection {0}", FacilityId).Single();

The error message is not helpful at all. First with the above line, it says, "InvalidOperationException: FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it. Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side."

What?

So, I add AsEnumerable and then it throws, "InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.String'."

What on Earth have they done. This is not intuitive at all.

entity-framework-core
1 Answers

FromSqlRaw or FromSqlInterpolated was called with non-composable SQL

The non-composable SQL is the one which cannot be converted to subquery select * from (your_sql). Calling SP (EXEC …) is one of the non-composable constructs.

and with a query composing over it

Non query returning LINQ operators like Single, First, Count, Max, Sum etc. require composing over the provided SQL query, for instance select count * from (your_query).

You can read more about it in Raw SQL queries - Composing with LINQ documentation topic, which also contains the "calling SP" and other limitations/restrictions:

Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. SQL queries that can be composed on begin with the SELECT keyword. Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

  • A trailing semicolon
  • On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • On SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

With that being said, inserting AsEnumerable() before Single() should really work.

The new exception you are getting is either EF Core bug or data type mapping issue (either you are passing int to string parameter, or SP is returning int for string class property). You need to examine the exception stack trace and/or compare your SP parameter and column types to FacilityId argument type and NoticeOfInspection class property types/mappings.

user_52025630
Related