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.
I used a table-valued function instead, it is composable since EFC knows that it is read only.