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.

I upgraded Visual Studio to 16.6.3 and restarted it and I can no longer reproduce the problem. I'm not sure if upgrading made a difference or just restarting Visual Studio. — Rn222, Jul 09 '20 at 17:43

score 4 · Answer 1 · answered Aug 07 '20 at 02:06

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.

score 1 · Answer 2 · answered Jul 08 '20 at 17:18

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();

I tried following your example: `var result = await context.Set().FromSqlRaw("EXECUTE GetTicket @TicketId", parameters).FirstOrDefaultAsync();` or the non-async version: `var result = context.Set().FromSqlRaw("EXECUTE GetTicket @TicketId", parameters).AsEnumerable().FirstOrDefault();` but I still get the same exception. I wonder if there is something wrong with my EF model. — Rn222, Jul 08 '20 at 19:43
You could try a rebuild of the EF code (check your SQL connection does not have any security restrictions) although I have the SPROC code manually written into another partial class that extends the DB Context. I use - Scaffold-DbContext "Server=localhost;Database=YourDB;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -o Models -force — , Jul 09 '20 at 10:09

score 0 · Answer 3 · answered May 11 '21 at 11:16

0

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();

answered May 11 '21 at 11:16

Pramod

103
4

You should note that this does the select part on the client side (ie not the database), AsEnumerable will run the query so far, so you will still get all columns returned from the DB, then you are selecting just the one column from that returned set. – Peter Kerr May 11 '21 at 14:11

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

3 Answers3