EF Core FromSqlRaw not executing stored procedure

Question

I am using EF Core Version 3.1.9.

The code shown here is to execute a stored procedure - but it does not seem to work:

var test1 = "abc";
var test2 = "xyz";

EmpResponse emp = await _context.Emp
                                .FromSqlRaw("EXECUTE EMPS..sel_keys @param1 = 20000, @param2 = {0}, @param3 = {1}", test1, test2)
                                .FirstOrDefaultAsync();

I get this error:

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.

I found the below document and it suggest to modify using AsEnumerable().FirstOrDefault()

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#fromsql

But since I am using await, the above suggestion does not work. Could someone suggest how to do it with await?

Answer 1

The simplest is to await a call to ToListAsync() and then apply {First|Single|Last}{OrDefault}, e.g.

var emp = (await _context.Emp
    .FromSqlRaw("EXECUTE EMPS..sel_keys @param1 = 20000, @param2 = {0}, @param3 = {1}", test1, test2)
    .ToListAsync())
    .FirstOrDefault();

---

In general they (EF Core designers) are expecting you to just insert AsAsyncEnumerable()

var emp = await _context.Emp
    .FromSqlRaw("EXECUTE EMPS..sel_keys @param1 = 20000, @param2 = {0}, @param3 = {1}", test1, test2)
    .AsAsyncEnumerable() // <--
    .FirstOrDefaultAsync();

but that first requires installing additionally System.Linq.Async package, and second - once you do that, you start getting a lot of ambiguous call compile-time errors when you access DbSets in queries, because they implement both IQueryable<T> and IAsyncEnumerable<T>.

So you'd better stay with the first solution until this gets resolved (I think it would be in EF Core 6).