Chain .Join() after .FromSqlRaw() in LinQ-CodePudding

I'm trying to chain a .Join() after a call to a stored procedure SP via .FromRawSql() in LinQ.

This exception gets raised:

System.InvalidOperationException: ''FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.'

But I absolutely need this query to work server side as it is quite heavy and takes more than 15 seconds to execute on the client side.

I've searched online but couldn't find anyone trying to join the result of .FromSqlRaw() with another table in LinQ, but perhaps I've missed something. Is it even possible? In that case, is there another way to perform this query server side?

A cleaned sample of my code:

await myContext.TableA
        .FromSqlRaw("SP")
        .Join(
          myContext.TableA,
          SPResult => new { a = SPResult.PK },
          TableA => new { a = TableA.PK },
          (SPResult, TableA)=> new
          {
            SPResult,
            TableA
          })
        .Select(x => x.SPResult)
        .ToListAsync();

CodePudding user response：

I realized that the JOIN I wanted to add was unnecessary, so my question is not relevant anymore.

However, the comments mentioned using a table-valued function instead and it seems like a good alternative to what I was trying to do.