Solved Issue: 'FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it'

We encountered the error message "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.'" error typically occurs when we attempt to execute a SQL query using Entity Framework Core's FromSqlRaw or FromSqlInterpolated methods with SQL that cannot be composed into a valid SQL statement by the database provider.

To solved this issue, we need to understand that Entity Framework Core aims to compose LINQ queries into SQL statements that can be executed on the database server. However, if the SQL query passed to FromSqlRaw or FromSqlInterpolated cannot be translated into a valid SQL statement by the database provider, such as when it contains server-side functions or constructs that the provider does not support, the error occurs.

One common scenario where this error might occur is when attempting to use database functions or operators that are not supported by the provider. For example, let's say we have the following code:

var result = dbContext.Customers
                     .FromSqlRaw("SELECT * FROM Customers WHERE ServerFunction(column) = 1")
                     .ToList();
  

In this example, if "ServerFunction" is a server-side function that cannot be translated by the database provider, we'll encounter the InvalidOperationException. To resolve this, we can modify the query to execute the function on the client-side by calling AsEnumerable before performing further LINQ operations:

var result = dbContext.Customers
                     .FromSqlRaw("SELECT * FROM Customers")
                     .AsEnumerable()
                     .Where(entity => ServerFunction(entity.Column) == 1)
                     .ToList();
  

By calling AsEnumerable, we indicate to Entity Framework Core that the subsequent LINQ operations should be performed on the client side, allowing us to utilize functions or constructs that may not be supported by the database provider.

While working on our project, we encountered an issue with using stored procedures to search the database. The stored procedures return several fields related to the amount of data returned. Our models are structured as follows:

public class ProductSearch {
  public string Id { get; set; }
  public string Query { get; set; }
  public SearchResultDTO Result { get; set; }
}
  

In the DBContext, we are using OwnsOne() to link the classes, following the guidance from Microsoft:

public DbSet ProductSearch{ get; set; }

// OnModelCreating
builder.Entity().OwnsOne(t => t.Result);
  

However, when making the stored procedure call using FromSqlRaw(), we encountered the following error:

System.InvalidOperationException: 'FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it.

I also tried changing ToList() to AsEnumerable() did not resolve the issue. Removing the OwnsOne() method and the Result property from the search class allowed the stored procedure to work and return results but not worked, error indicates that there is a problem with composing the SQL query when using FromSqlRaw() with non-composable SQL. 

var result = db.ProductSearch
    .FromSqlRaw("EXECUTE [Search].[Foo] {0}, {1}", foo, bar)
    .IgnoreQueryFilters()
    .ToList();
  

While working with a simple stored procedure called using the FromSqlRaw command, we encountered an error stating:

'FromSql' or 'SqlQuery' 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.

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

To resolve this issue, we modified our code to use ToList() after FromSqlRaw:

db.Employees.FromSqlRaw("EXEC dbo.sp_Getemployees").ToList().First();
  

By calling ToList() immediately after FromSqlRaw, we force the query to be executed and the results to be materialized into a list on the client side. This allows us to work with the query results as a collection of objects, resolving the error.

While working with Entity Framework, we encountered an error stating: "FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it." This error occurred when using OwnsOne() in our code.

To resolve this issue, you need to ensure that the SQL query passed to FromSqlRaw or FromSqlInterpolated is composable, meaning it can be further composed or modified by Entity Framework. When using OwnsOne(), it's important to ensure that the SQL query does not include any non-composable operations.

Here's an example of how we can modify our code to resolve this error:

var result = dbContext.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Id= 2")
    .ToList();
  

In above example, we ensure that the SQL query passed to FromSqlRaw is composable and does not include any non-composable operations. By doing so, we can avoid the error related to non-composable SQL queries.