FromSql method when used with stored procedure cannot be composed error

[robinwilson16]

It would be useful if this breaking changes document included how to handle the breaking change when amending EF code for stored procedures that return a single entity.

The suggested fix is to change

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

To

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

However whilst this works with tables it does not work for stored procedures where a single item is returned and no information on how to handle this is provided. After some trial and error I found this worked:

(await _context.Products.FromSqlInterpolated($"EXEC GetProducts").ToListAsync()).FirstOrDefault();

The issue does not apply for a list of items where this works:

await _context.Products.FromSqlInterpolated($"EXEC GetProducts").ToListAsync();

Could the docs be updated for next time I need to remind myself how I did this?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: e4d0e044-b0cb-21e2-19c1-2f137da671ac
• Version Independent ID: 1cbedd95-8640-1649-9967-d0ef71633534
• Content: Breaking changes in EF Core 3.0 - EF Core
• Content Source: entity-framework/core/what-is-new/ef-core-3.0/breaking-changes.md
• Product: entity-framework
• Technology: entity-framework-core
• GitHub Login: @ajcvickers
• Microsoft Alias: avickers

[ajcvickers]

Notes from triage: we should document using IX-Async for this:

var result = await context.Set<Product>()
    .FromSqlRaw("[dbo].[Ten Most Expensive Products]")
    .AsAsyncEnumerable()
    .FirstOrDefaultAsync();

We might also consider showing how to write some of these extensions manually to avoid the dependency. For example:

public static class AsyncEnumerableExtensions
{
    public static async Task<TSource> FirstOrDefaultAsync<TSource>(this IAsyncEnumerable<TSource> source)
    {
        await using var e = source.GetAsyncEnumerator();
        return await e.MoveNextAsync() ? e.Current : default;
    }
}