EF Core server evaluation of filter on stored procedure result

c# entity-framework entity-framework-core sql-server

Question

EF Core supports executing stored procedures and mapping the results to an entity (either existing entities or entities created specifically for the stored procedure).

I've read many issues around this topic, such as https://github.com/aspnet/EntityFrameworkCore/issues/1862 https://github.com/aspnet/EntityFrameworkCore/issues/245.

I thought that #1862 would enable server evaluation for stored procedures that only perform a SELECT operation.

However, it seems that my code results in 2 calls to the database. Is there a way to execute the following code with a single call to the database?

var customIdsQuery = _dbContext.Set<CustomId>()
            .FromSql($"dbo.GetCustomIDs {username}").AsNoTracking();    
var eventsQuery = _dbContext.Event.Where(ev => ev.User.Username == username)
            .Join(customIdsQuery, ev => rev.CustomId, cid=> cid.Id, (ev, cid) => ev).AsNoTracking();
var events = eventsQuery.ToList(); // 2 database calls

The model builder looks something like that:

modelBuilder.Entity<CustomId>(); // For the stored procedure
modelBuilder.Entity<Event>(entity => // Key is defined as attribute on the model
        {
            entity.HasOne(d => d.Meeting)
                .WithMany(p => p.Event)
                .HasForeignKey(d => d.MeetingId);
            entity.HasOne(d => d.User)
                .WithMany(p => p.Event)
                .HasForeignKey(d => d.UserId);
        });

Am I missing something obvious in order to be able to have the entire query evaluate on the SQL server? From the documentation (https://docs.microsoft.com/en-us/ef/core/querying/raw-sql):

SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (e.g. to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery

My stored procedure is in fact like a SELECT statement, is there a way to enforce it being recognized as such?

Thanks.

1
0
4/15/2018 11:48:55 AM

Accepted Answer

I used a table-valued function instead, it is composable since EFC knows that it is read only.

0
4/20/2018 9:46:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow