Include with FromSqlRaw and stored procedure in EF Core 3.1

Question

So here's the deal - I am currently using EF Core 3.1 and let's say I have an entity:

public class Entity
{
    public int Id { get; set; }
    public int AnotherEntityId { get; set; }
    public virtual AnotherEntity AnotherEntity { get; set; }
}

When I access the DbSet<Entity> Entities normal way, I include AnotherEntity like:

_context.Entities.Include(e => e.AnotherEntity)

and this works. Why wouldn't it, right? Then I go with:

_context.Entities.FromSqlRaw("SELECT * FROM Entities").Include(e => e.AnotherEntity)

and this also works. Both return me the same collection of objects joined with AnotherEntity. Then I use a stored procedure which consists of the same query SELECT * FROM Entities named spGetEntities:

_context.Entities.FromSqlRaw("spGetEntities")

guess what? This also works. It gives me the same output but without joined AnotherEntity, obviously. However if I try to add the Include like this:

_context.Entities.FromSqlRaw("spGetEntities").Include(e => e.AnotherEntity)

I am getting:

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.

Even though the output of _context.Entities.FromSqlRaw("SELECT * FROM Entities") and _context.Entities.FromSqlRaw("spGetEntities") is identical.

I couldn't find a proof that I can or I can not do this with EF Core 3.1 but if someone could give me any hint of possibility of this approach it would be nice.

Also if there is another way to get joined entities using stored procedure I would probably accept it as the solution of my issue.

Qualified

It's not EF that can't do this. It's SQL itself ("non-composable SQL"), so let alone EF could. – Fluttery

@GertArnold please add it as an answer. It will help other users too. – Impulsive

_context.Something.FromSqlRaw("EXECUTE dbo.spCreateSomething @Id, @Year", sqlParameters).IgnoreQueryFilters().AsNoTracking().AsEnumerable().FirstOrDefault(); This works for me, you can use ToList() as well over .AsEnumerable().FirstOrDefault() to get many. – Masaccio

Seeing all but the accepted answers below, for whatever reason it seems unclear that this is about using Include, not about how to use FromSqlRaw. The accepted answer is all there is to it. – Fluttery

Answer 1

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

Pastrami

This does not work in my case because I am using a derived type. When using derived type from the type that is used in the model, the query is composing even if you call AsEnumerable right after FromSqlRaw. I see no other solution but to make that type not derived, separate with all of the properties from the base type, which is not convenient. – Midgut

@HrvojeBatrnek I guess you have in mind #61071435 – Pastrami

Answer 2

In my case I was converting working EF FromSql() with a stored procedure 2.1 code to 3.1. Like so:

ctx.Ledger_Accounts.FromSql("AccountSums @from, @until, @administrationId",
                                                            new SqlParameter("from", from),
                                                            new SqlParameter("until", until),
                                                            new SqlParameter("administrationId", administrationId));

Where AccountSums is a SP.

The only thing I had to do was use FromSqlRaw() and add IgnoreQueryFilters() to get it working again. Like so:

ctx.Ledger_Accounts.FromSqlRaw("AccountSums @from, @until, @administrationId",
               new SqlParameter("from", from),
               new SqlParameter("until", until),
               new SqlParameter("administrationId", administrationId)).IgnoreQueryFilters();

This is mentioned in the comments, but I missed that at first so including this here.

Damnable

so AccountSums is an SP and @from is another join which include entity sub navigation? seems really odd.. to do this way.. could you give you a usage example. – Giuliana

And SP parameters – Damnable

ok but then does this answer the original question? as my understanding is no... maybe i missed something tho – Giuliana

If you get a chance please share more of your code. I tried your approach and I still get the darn "non-composable SQL" error. I'm using .Net 6/EntityFrameworkCore 6.0.1 though, it might have broken again. – Dennet

There is no more code.. I guess you have a different problem – Damnable

Please read the question. How does this solve the Include issue? – Fluttery

Answer 3

The solution for me was to add .AsEnumerable() before performing any operation on the result of the raw sql query.

this fails

var results = ExecuteMyRawSqlQuery(); 
results.Select(r=> r.MyColumn).ToList();

this works

var results = ExecuteMyRawSqlQuery();
results.AsEnumerable().Select(r=> r.MyColumn).ToList();

Hip

Hi. You should be aware that as soon as you apply .AsEnumerable() to your db set, all the records get evaluated into memory. Thus, any further actions of transforming, filtering and ordering your data are performed on the executing machine. – Qualified

Please read the question. How does this solve the Include issue? – Fluttery

Answer 4

My solution in the end was to modify my stored procedure to return only Id's. Then I would use EF and find all of my parent object and include the child object. I did need to create a "dummy" entity called "IdTable" but this works and really fast. My stored procedure has some complicated logic in it so I want to keep that fast and then let EF do the rest of the relational data and entity mapping.

This example is from MySQL but MsSQL would work similarly with "Exec" instead of "CALL"

 var blogIds = await _persistenceManager.Context.Set<IdTable>().FromSqlRaw($"CALL getBlogIds({query};").ToListAsync();

 return _persistenceManager.GetEntities<Blog>().AsNoTracking()
                    .Include(i => i.Tags)
                    .Where(pa => blogIds.Select(i => i.Id).Contains(pa.Id));

Here is the "dummy" model

public partial class IdTable
{
    [Key]
    public int Id { get; set; }
}

And for the DBContext

public virtual DbSet<IdTable> IdTables { get; set; }

Lamberto