How do you call a stored procedure in EF Core and return the results using a generic class?

How can you call a stored procedure using Entity Framework Core and return the results using a generic class?

>Solution :

Assuming you’re using Code First and are comfortable with the steps necessary to create the actual stored procedure and have that defined in your database, I will focus my answer on how to call that stored procedure in C# and map the results generically.

First you want to create a model that matches the data you expect to get back from your results.

Here is an example:

public class UserTimesheet : IStoredProcedure
{
    public string Query => "[dbo].[GetUserTimesheet] @userId, @month, @year";

    public DateTime WorkDate { get; set; }
    public Guid ProjectId { get; set; }
    public int CategoryId { get; set; }
    public string? ProjectName { get; set; }
    public decimal? Hours { get; set; }
}

Notice this extends an interface called IStoredProcedure with the Query property. More on that later, but it’s there to work by convention.

Here is that interface:

public interface IStoredProcedure
{
    string Query { get; }
}

Next you’ll want to add a DbSet to your database context.

// Put this in your Database Context
public DbSet<UserTimesheet> UserTimesheets { get; set; } = null!;

Now since this doesn’t map to an actual table, you will want to add some code to the OnModelCreating to tell EF how to reference it. Again, I’m working on a convention, in this case I only want to apply this setting to models that implement IStoredProcedure, and we can do that with a little reflection to make life easier.

In this case we’re going to say it has no key and treat it like a view. I created an extension method to keep things a little cleaner, you can use it like this:

public static class ModelBuilderExtension
{
    public static ModelBuilder ConfigureStoredProcedureDbSets(this ModelBuilder modelBuilder)
    {
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (typeof(IStoredProcedure).IsAssignableFrom(entityType.ClrType))
            {
                modelBuilder.Entity(entityType.ClrType).HasNoKey().ToView(null);
            }
        }

        return modelBuilder;
    }
}


// Put this in DatabaseContext class

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.ConfigureStoredProcedureDbSets();
}

Next you’ll want something to hold your generic code. I use a class called StoredProcedureRepository, but you can call it what you like. Here’s the code, including the interface (for Dependency Injection, if you like):

public interface IStoredProcedureRepository
{
    IEnumerable<T> ExecuteStoredProcedure<T>(object[] sqlParameters) where T : class, IStoredProcedure, new();
    SqlParameter GetSqlParameter(string name, object value, bool isOutput = false);
}

public abstract class Repository
{
    protected readonly DatabaseContext _context;

    protected Repository(DatabaseContext context)
    {
        _context = context;
    }
}

public class StoredProcedureRepository : Repository, IStoredProcedureRepository
{
    #region Properties

    private const string SQL_PARAMETER_PREFIX = "@";

    #endregion

    #region Constructor

    public StoredProcedureRepository(DatabaseContext context) : base(context)
    {
    }

    #endregion

    #region Shared Public Methods

    public IEnumerable<T> ExecuteStoredProcedure<T>(object[] sqlParameters) where T : class, IStoredProcedure, new()
    {
        return _context.Set<T>().FromSqlRaw<T>((new T()).Query, sqlParameters).ToList();
    }

    public SqlParameter GetSqlParameter(string name, object value, bool isOutput = false)
    {
        if (!name.StartsWith(SQL_PARAMETER_PREFIX))
        {
            name += SQL_PARAMETER_PREFIX;
        }

        var direction = isOutput ? System.Data.ParameterDirection.Output : System.Data.ParameterDirection.Input;

        return new SqlParameter
        {
            ParameterName = name,
            Value = value,
            Direction = direction
        };
    }

    #endregion
}

There are a few things to notice here. It’s referencing the DbSet generically and using FromRawSql and calling the Query (string) property from the IStoredProcedure implementation of your model. So you’ll want to make sure that contains your query to execute the stored procedure, in this example that would be "[dbo].[GetUserTimesheet] @userId, @month, @year"

Now you can call this stored procedure generically.

Here is an example:

var parameters = new object[3];
parameters[0] = GetSqlParameter("@userId", userId);
parameters[1] = GetSqlParameter("@month", month);
parameters[2] = GetSqlParameter("@year", year);

IList<UserTimesheet> queryResults = _storedProcedureRepository.ExecuteStoredProcedure<UserTimesheet>(parameters).ToList();

To add new stored procedures, just create their respective models (being sure to implement IStoredProcedure and define their Query property, then add their DbSet to the database context.

For example:

public class UserProject : IStoredProcedure
{
    public string Query => "[dbo].[GetUserProjects] @userId";

    public Guid ProjectId { get; set; }
    public string ProjectName { get; set; }
}

// add this to the database context
public DbSet<UserProject> UserProjects = null!;

then call it like so:

var parameters = new object[1];
parameters[0] = GetSqlParameter("@userId", userId);

IList<UserProject> queryResults = _storedProcedureRepository.ExecuteStoredProcedure<UserProject>(parameters).ToList();