FromSql method when used with stored procedure cannot be composed in EF Core 3.1

Question

I'm trying to call a stored procedure to retrieve a single record using EF Core but I keep getting the exception:

System.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.'

Here: ef-core-3.0 breaking-changes it is recommended to use use .AsEnumerable() but it has no effect. I don't see why it thinks I'm trying to compose over this SQL code:

var result =  context.Set<TicketDetails>()
                    .FromSqlInterpolated($"EXECUTE GetTicket @TicketId = {id}")
                    .AsEnumerable()
                    .FirstOrDefault();

Also here is a similar issue that didn't give a solution for me.

Answer 1

I'm using a different mechanism that does work - so you can return one or more rows into a C# class

Here is my DB Set of the stored proc

/// <summary>
/// Stored Proc Visits
/// </summary>
public virtual DbSet<Visits> SP_Visits { get; set; }

Here is the code that returns a list but you would replace the last part with FirstOrDefaultAsync. You MUST ensure that the parameters are in the same order as the SQL despite creating them named - the DBContext code just ignores that. You can also set parameters in SQL such as @Sort=@Sort which does work by matching names rather than order

    SqlParameter[] parameters = {
            new SqlParameter("DateFrom", dateFrom),
            new SqlParameter("DateTo", dateTo),
            new SqlParameter("Aggregation", aggregation),
            new SqlParameter("Sort", sort)
                };


        return await SP_Visits.FromSqlRaw("EXECUTE dbo.sp_Visits @DateFrom, @DateTo, @Aggregation, @Sort", parameters).ToListAsync();

Answer 2

One issue that I found, though it may not apply in this case, was that I had an object that inherited from another class and if I called a stored proc for either class I got the error message. Once I removed the inheritance and copied all the props to the child class, everything worked again.

I had:

public class Role 
{
    public int RoleID { get; set; }
    public string RoleName { get; set; }
}

and

public class UserRole :  Role
{
    public string Email { get; set; }
    public int? UserRoleID { get; set; }
}

I changed it to:

public class UserRole
{
    public string Email { get; set; }
    public int? UserRoleID { get; set; }
    public int RoleID { get; set; }
    public string RoleName { get; set; }
}

After that, everything worked again.

Answer 3

The solution for me was to add .AsEnumerable() before performing any operation on the result of the raw sql query.

var results = ExecuteMyRawSqlQuery();
results.AsEnumerable().Select(r=> r.MyColumn).ToList();