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

Question

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

Alternatively, are you able to rewrite the stored procedure as a table-valued function (assuming this is SQL Server or some other RDBMS with a similar feature)? EF Core 6+ has excellent TVF mapping support. — madreflection, Dec 20 '22 at 15:58
SQL Server can't, so EF can't. See [How can I join on a stored procedure?](https://stackoverflow.com/questions/921190/how-can-i-join-on-a-stored-procedure). Move all the logic into a new SP. — CodeCaster, Dec 20 '22 at 15:58
You can't use stored procedures in queries. You need to use a table-valued function instead. EF or any ORM can't do things that are impossible in SQL. Besides, ORMs deal with *entities* not tables. Their job is to give the impression of working with in-memory objects instead of tables. The very fact you had to write so much code is a very, very strong indication that you're using EF wrong. You're trying to use objects as if they were tables — Panagiotis Kanavos, Dec 21 '22 at 08:19
If the stored procedure and JOIN are slow, you should investigate the problem and fix it. Stored procedures don't make slow queries run faster. JOINing a slow query/view/function with another table will only make things slower, especially if the query engine can't use indexes to join the two result sets. — Panagiotis Kanavos, Dec 21 '22 at 08:25
@A.D. I you can't create SQL which use `JOIN` to Stored Procedure (as i know you can't) - ORM will not help. Convert your SP to table valued-function and things will become better. — Svyatoslav Danyliv, Dec 21 '22 at 09:01
I didn't know it was not possible in SQL, so it makes sense that it can't work with EF. After giving it some thought I realized that the JOIN I wanted to add was absolutely unnecessary. Stupid me! I should have better analysed the problem. However, thank you for your explanations and ideas, I've definitely learned some. — pedral, Dec 21 '22 at 09:22

score 0 · Accepted Answer · answered Dec 21 '22 at 09:26

0

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.

answered Dec 21 '22 at 09:26

pedral

46
8

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

1 Answers1