Include with FromSqlRaw and stored procedure in EF Core

If we run the above query we will get the below error:

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.

As we cannot use the Include method with FromSqlRaw and stored procedures in EF Core. This is because the Include method is designed to work with entity framework's LINQ queries, allowing us to eagerly load related entities and FromSqlRaw is used specifically for executing raw SQL queries or stored procedures directly against the database, and it does not support eager loading of related entities through Include.

When we use FromSqlRaw to execute a stored procedure, EF Core treats the result as a raw result set and does not interpret it as entity objects with navigational properties that could be eager-loaded using Include. Therefore, attempts to use Include with FromSqlRaw will result in an error or unexpected behavior.

To achieve similar functionality when working with stored procedures, we may need to manually load related entities after executing the stored procedure and mapping the results to entities. 

We often required task where we need to use Entity Framework Core (EF Core) to execute a stored procedure and include related entities in the result. However, when using FromSqlRaw to execute a stored procedure, we might face challenges with including related entities due to limitations in the SQL query composition process.

To resolve this issue, we can follow a workaround that involves executing the stored procedure using FromSqlRaw to retrieve the primary results and then perform additional queries to include related entities. Let's illustrate this with an example:

var result = dbContext.MyEntities
                     .FromSqlRaw("EXEC MyStoredProcedure @param1, @param2", param1Value, param2Value)
                     .ToList();

var relatedEntities = dbContext.OtherEntities
                              .Where(e => result.Select(r => r.Id).Contains(e.ParentEntityId))
                              .ToList();

foreach (var entity in result)
{
    entity.RelatedEntities = relatedEntities.Where(e => e.ParentEntityId == entity.Id).ToList();
}
  

In this example, we first execute the stored procedure "MyStoredProcedure" using FromSqlRaw, passing any necessary parameters. This retrieves the primary result set. Then, we perform a separate query to fetch the related entities that we want to include.

Finally, we manually associate the related entities with their corresponding parent entities by iterating through the primary result set and populating the "RelatedEntities" navigation property accordingly.

Although this approach involves additional queries and manual association, it allows us to include related entities when executing stored procedures with FromSqlRaw in EF Core.