Include with FromSqlRaw and stored procedure in EF Core 3.1

by

Shortly, you can’t do that (at least for SqlServer). The explanation is contained in EF Core documentation – Raw SQL Queries – Composing with LINQ:

Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. SQL queries that can be composed on begin with the SELECT keyword. Further, SQL passed shouldn’t contain any characters or options that aren’t valid on a subquery, such as:

  • A trailing semicolon
  • On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • On SQL Server, an ORDER BY clause that isn’t used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

SQL Server doesn’t allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL. Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn’t try to compose over a stored procedure.

Additionally, since Include / ThenInclude require EF Core IQueryable<>, AsEnumerable / AsAsyncEnumerable etc. is not an option. You really need composable SQL, hence stored procedures are no option.

Instead of stored procedures though, you can use Table-Valued Functions (TVF) or database views because they are composable (select * from TVF(params) or select * from db_view) .

More Related Contents:

  1. Could not load file or assembly Microsoft.Extensions.DependencyInjection.Abstractions, Version=1.1.0.0
  2. VB Net Class in C# [closed]
  3. Entering keys manually with Entity Framework
  4. How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?
  5. A circular reference was detected while serializing an object of type ‘SubSonic.Schema .DatabaseColumn’.
  6. The cast to value type ‘Int32’ failed because the materialized value is null
  7. Making Entity Class Closed for Changes
  8. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  9. Filter/Search using Multiple Fields – ASP.NET MVC
  10. EF Data Context – Async/Await & Multithreading
  11. Why would try/finally rather than a “using” statement help avoid a race condition?
  12. Retrieve an object from entityframework without ONE field
  13. How can I disable model compatibility checking in Entity Framework 4.3?
  14. DbContext discard changes without disposing
  15. Entity framework EF.Functions.Like vs string.Contains
  16. Entity Framework 4: How to find the primary key?
  17. .Include() vs .Load() performance in EntityFramework
  18. Entity Framework | Sequence contains more than one matching element
  19. Max return value if empty query
  20. Group by with multiple columns using lambda
  21. Working with SQL views in Entity Framework Core
  22. How to do an “in” query in entity framework?
  23. How to Specify Primary Key Name in EF-Code-First
  24. big issue in converting string to datetime using linq-to-entities
  25. Entity Framework: Duplicate Records in Many-to-Many relationship
  26. Keep Getting ‘The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities’ Exception
  27. How to include() nested child entity in linq
  28. The ObjectContext instance has been disposed and can no longer be used for operations that require a connection [duplicate]
  29. Should the order of LINQ query clauses affect Entity Framework performance?
  30. How can set a default value constraint with Entity Framework 6 Code First?

Leave a Comment