Include with FromSqlRaw and stored procedure in EF Core 3.1
In my case I was converting working EF FromSql()
with a stored procedure 2.1 code to 3.1.
Like so:
ctx.Ledger_Accounts.FromSql("AccountSums @from, @until, @administrationId",
new SqlParameter("from", from),
new SqlParameter("until", until),
new SqlParameter("administrationId", administrationId));
Where AccountSums
is a SP.
The only thing I had to do was use FromSqlRaw()
and add IgnoreQueryFilters()
to get it working again. Like so:
ctx.Ledger_Accounts.FromSqlRaw("AccountSums @from, @until, @administrationId",
new SqlParameter("from", from),
new SqlParameter("until", until),
new SqlParameter("administrationId", administrationId)).IgnoreQueryFilters();
This is mentioned in the comments, but I missed that at first so including this here.
Shortly, you can't do that (at least for SqlServer). The explanation is contained in EF Core documentation - Raw SQL Queries - Composing with LINQ:
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 withOFFSET 0 OR TOP 100 PERCENT
in theSELECT
clauseSQL 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
orAsAsyncEnumerable
method right afterFromSqlRaw
orFromSqlInterpolated
methods to make sure that EF Core doesn't try to compose over a stored procedure.
Additionally, since Include
/ ThenInclude
require EF Core IQueryable<>
, AsEnumerable
/ AsAsyncEnumerable
etc. is not an option. You really need composable SQL, hence stored procedures are no option.
Instead of stored procedures though, you can use Table-Valued Functions (TVF) or database views because they are composable (select * from TVF(params)
or select * from db_view
) .