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

Question

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!

Twentyone

Answer 1

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.

Matriculate

Good answer, at least it's a definite 'no'. I can work with that. :) – Twentyone

It worked, just for others' reference here's the TVF call I used, (Post is my entity class): IEnumerable<Post> targetPosts = context.Posts.FromSqlInterpolated($"SELECT * FROM Post.UDF_MY_TVF({id},{authorizedUser.ID})"); Post targetPost = targetPosts.FirstOrDefault<Post>(); And my UDF was basically the exact same as my sproc, just as a UDF. One note, I had to actually include the Discriminator column in the TVF return table, even though it's obviously not in my Post entity, since EF expects every column from the nested select to be present. – Twentyone

Y'all got an example because I'm not understanding this... – Orlan

Hi @Ortund, do you really need to understand it? It's quite specific to EF Core implementation of Database Inheritance and Raw SQL Queries. – Matriculate