Possible to call a stored procedure on a Table-Per-Hierarchy table in EF Core 3.1?

user_3484590 0
Viewed 877

I'm moving from EF Core 2.2 to 3.1. One breaking change (#15392) was that it no longer composed over stored procedures, so you had to add 'AsEnumerable'. That usually works, but I have a stored procedure call on a TPH table where that fails:

  1. My call to the SPROC is:

     SqlParameter authorizedUserID_p = 
          new SqlParameter("@authorizedUserID", authorizedUser.ID);
 IEnumerable<Post> query = 
          context.Posts.FromSqlRaw<Post>("Post.USP_ReadPost @ID, @AuthorizedUserID",
            parameters: new[]{ parentID_p, authorizedUserID_p }
        ).AsEnumerable<Post>();
 Post targetPost = query.ToList<Post>().FirstOrDefault<Post>();

  2. And it produces this error, recommending using AsEnumberable (which I'm already using above):

    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.

  3. I believe the reason is because my Posts table is Table-per-hiearchy, as other calls to SPROCS in the same application are working fine. Would appreciate any help possible!

table-per-hierarchy entity-framework-core
1 Answers

This is yet another issue introduced by EFC 3, tracked by #18232: Impossible to use stored procedures related to entities that inherits another one.

The reason is that SP calls are not composable, and EF Core always try to compose SQL for TPH base entities in order to add discriminator condition. Similar to Global Query Filters, but there you can at least use IgnoreQueryFilters, while here you have no option.

The good news is that it's already fixed in EFC repository. The bad news is that it won't be released until EFC 5.0.

Since AsEnumerable() doesn't help, all you can do is to wait for EFC 5.0. Or, if possible, convert SPs like this to TVF (table valued functions) which are composable. In general, use scalar functions or stored procedures with output parameter(s) for non query returning calls (to be executed with ExecuteSql*), and table valued functions for single query returning calls (to be used with FromSql*). Note that currently EFC does not support multiple query returning stored procedures anyway.

user_52025630
Related