EF core 3.1 can not run complex raw sql query

Question

The following query was working fine with EF core 2 but EF core 3 would throw error! I even could add some include after this query in EF core 2 which I let go now.

query:

// just to have an Id
var id = Guid.NewGuid();
var resutl = Context.Parties.FromSqlInterpolated($@"WITH mainOffice AS 
             (SELECT * FROM Parties as o1 WHERE (Discriminator = N'Office')
              AND (Id = '{id}') 
              UNION ALL SELECT o.* FROM Parties AS o INNER JOIN mainOffice AS m 
              ON m.Id = o.ParentOfficeId)
              SELECT * FROM mainOffice as f").ToList();

The error it produces is as follows:

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.

Knowing the following information might help:

• Table "Parties" is a table per hierarchy
• I tried to run the query both from the root type DbSet and the type I am interested for
• No success with nether FromSqlRaw nor FromSqlInterpolated
• Adding 'AsEnumerable' did not help too

Did I forget any thing? What am I doing wrong? What does 'non-composable SQL' mean? Does it mean EF core is trying to interpret query?

Answer 1

I found a workaround for this issue,

You can create a view in database and a query type in your model and then run your query against this view (note that the way you do that has been changed from ef 2 to 3 as it is explained here)

So in this way inheritance and discriminators are not problems any more and query can be run.