‘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.

Explanation:

The error message “fromsqlraw’ or ‘fromsqlinterpolated’ was called with non-composable sql and with a query composing over it” typically occurs when using the FromSqlRaw or FromSqlInterpolated methods in Entity Framework Core, and the SQL query being executed is not compatible with the IQueryable composition.

To understand this error, let’s break it down with an example:

var query = context.SomeEntity.FromSqlRaw("SELECT * FROM SomeTable WHERE SomeProperty = {0}", someValue)
                             .Where(e => e.AnotherProperty == anotherValue);

In the above example, the FromSqlRaw method is used to execute a raw SQL query, and then further composition is performed on the query with the Where method. However, the error occurs because the SQL query provided to FromSqlRaw is not composable.

In order to resolve this error, the Enumerable.AsEnumerable method should be called after the FromSqlRaw or FromSqlInterpolated method to force the composition to be performed on the client side rather than at the database level:

var query = context.SomeEntity.FromSqlRaw("SELECT * FROM SomeTable WHERE SomeProperty = {0}", someValue)
                             .AsEnumerable()
                             .Where(e => e.AnotherProperty == anotherValue);

By calling AsEnumerable after the FromSqlRaw method, the query is executed against the database and the results are brought back to the client side. Then, the Where method can be used to perform additional filtering on the retrieved data.

It’s important to note that calling AsEnumerable will cause the subsequent operations to be performed in-memory rather than being translated to SQL. This can have performance implications, especially if the initial SQL query would have resulted in a smaller result set being retrieved from the database.