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.

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.

When working with databases in .NET, one of the most common tasks is querying data from a database and returning it as a collection of objects. In order to do this, you can use the LINQ (Language Integrated Query) framework provided by Microsoft.

One of the most commonly used methods for querying data from a database using LINQ is the `FromSqlRaw` or `FromSqlInterpolated` method. These methods allow you to specify a raw SQL query and execute it against a database, returning a collection of objects that match the query results.

However, there are some limitations to using these methods. One limitation is that they do not support composing over the query. This means that if you want to use multiple `FromSqlRaw` or `FromSqlInterpolated` methods in your LINQ query, you will need to manually concatenate the results of each method together.

To address this limitation, Microsoft has introduced a new feature called "query composing" in .NET 5. This feature allows you to compose multiple `FromSqlRaw` or `FromSqlInterpolated` methods together into a single LINQ query, without having to manually concatenate the results.

To use query composing, you will need to include the `Microsoft.EntityFrameworkCore.SqlServer` package in your project. This package provides the necessary classes and methods for working with SQL Server databases using LINQ.

Here is an example of how to use query composing to retrieve data from a SQL Server database

csharp
using Microsoft.EntityFrameworkCore;
using System.Linq;
// Create a DbContext class that inherits from DbContext
public class MyDbContext 
 DbContext
{
    // Define the DbSet for your model
    public DbSet MyModels { get; set; }
}
// Create a new instance of the DbContext class
var db = new MyDbContext();
// Use query composing to retrieve data from the database
var result = db.FromSqlRaw("SELECT * FROM MyTable WHERE Id = @Id")
    .Where(m => m.Id == 1)
    .AsEnumerable()
    .ToList();

In this example, we first create a `MyDbContext` class that inherits from `DbContext`, where `T` is the type of our model. We then define a `DbSet` for our model and create an instance of the `MyDbContext` class.

Next, we use query composing to retrieve data from the `MyTable` table in the database. We start by calling the `FromSqlRaw` method with a raw SQL query that selects all columns from the `MyTable` table where the `Id` column is equal to 1.

We then use the `Where` method to filter the results of the query, only returning rows where the `Id` column is equal to 1.

Finally, we call the `AsEnumerable` method to convert the result of the query into an enumerable collection, and then call the `ToList` method to convert the collection into a list of objects that can be used in our application.

One of the main benefits of using query composing is that it allows you to write more complex LINQ queries without having to manually concatenate the results of multiple methods together. This can make your code more readable and easier to maintain, especially when working with large datasets or complex queries.

In addition to `FromSqlRaw` and `FromSqlInterpolated`, query composing also supports other LINQ methods such as `Select`, `Join`, and `GroupBy`. This allows you to write more powerful and flexible LINQ queries that can be used with a wide range of databases and data sources.

Another benefit of using query composing is that it allows you to take advantage of the latest features in the .NET framework, such as async/await and C# 9's `async` and `await` keywords. This makes it easier to write asynchronous LINQ queries that can be executed in parallel, improving performance and scalability.

In conclusion, query composing is a powerful new feature in .NET 5 that allows you to compose multiple LINQ methods together into a single query, without having to manually concatenate the results. This makes it easier to write more complex and flexible LINQ queries, and takes advantage of the latest features in the .NET framework. If you are working with databases or other data sources in your .NET applications, be sure to take advantage of this feature to improve the performance and scalability of your code.