Execute Stored Procedures

[alonsourena]

I was thinking if it's possible to execute a stored procedure using Entity Framework Core.

As stated in the Entity Framework Core docs is possible but:

FromSql can only be used directly on a DbSet. It cannot be composed over an arbitrary LINQ query.

I tried to use FromSql on the DbSet properties created on the IApplicationDbContext but it doesn't show up the method.

So will be nice to have a dynamic way to execute stored procedures.

[alonsourena]

I found a way to execute Stored Procedures, but I still have a problem loading the related entities.

Add ExecuteStoredProcedure method to the IApplicationDbContext interface.

public interface IApplicationDbContext
{
    IQueryable<T> ExecuteStoredProcedure<T>(string procedureName, params string[] parameters) where T : class;
}

On the implementation of the IApplicationDbContext interface, add the next code:

public class YourDbContext : IApplicationDbContext
{
    public IQueryable<T> ExecuteStoredProcedure<T>(string procedureName, params string[] parameters) where T : class
    {
        var dbSet = Set<T>();
        return dbSet.FromSqlRaw($"EXECUTE [{procedureName}] {string.Join(", ", parameters)}").IgnoreQueryFilters();
    }
}

And whenever we need to call a Store Procedure, we can call it this way:

var results = await _context.ExecuteStoredProcedure<YourEntity>("sp_YourStoredProcedureName", "parameter").ToListAsync(cancellationToken);