Post
Ask Question

Dotnet Core, EF Core Store Procedure With Multiple Results

Introduction

When utilizing Entity Framework Core and stored procedures to retrieve data from SQL databases, there is a limitation to be aware of. Stored procedures should consistently return all columns required for the properties present in the class registered as DbQuery<T>, facilitating the mapping of stored procedure results to entities.

However, certain scenarios may produce varying subsets of results based on specific logic. In this article, we will explore an alternative approach to overcoming this limitation.

1. Creating Extention for the DbContext to Initiate the Store Procedure call.

public static DbCommand StoreProcedure(this DbContext context, string storedProcName, bool prependDefaultSchema = true, short commandTimeout = 30)
{
    var cmd = context.Database.GetDbConnection().CreateCommand() ?? throw new ArgumentNullException(nameof(context));
    storedProcName = string.IsNullOrEmpty(storedProcName) ? throw new ArgumentException("Stored procedure name cannot be null or empty.", nameof(storedProcName)) : storedProcName;

    cmd.CommandTimeout = commandTimeout;
    if (prependDefaultSchema)
    {
        var schemaName = context.Model.Relational().DefaultSchema;
        if (!string.IsNullOrEmpty(schemaName))
        {
            storedProcName = $"{schemaName}.{storedProcName}";
        }
    }
    cmd.CommandText = storedProcName;
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    return cmd;
}

2. Creating Extention for the DbContext to Execute the Store Procedure.

public static void Execute(this DbCommand command, Action<Result> handleResults, System.Data.CommandBehavior commandBehaviour = System.Data.CommandBehavior.Default, bool manageConnection = true)
{
    if (handleResults == null)
    {
        throw new ArgumentNullException(nameof(handleResults));
    }
    using (command)
    {
        if (manageConnection && command.Connection.State == System.Data.ConnectionState.Closed)
        {
            command.Connection.Open();
        }

        try
        {
            using (var reader = command.ExecuteReader(commandBehaviour))
            {
                var sprocResults = new Result(reader);
                handleResults(sprocResults);
            }
        }
        finally
        {
            if (manageConnection && command.Connection.State == System.Data.ConnectionState.Open)
            {
                command.Connection.Close();
            }
        }
    }
}

3. Methods to access DbDataReader.

public class Result
{
    private DbDataReader _reader;
    public Result(DbDataReader reader)
    {
        _reader = reader;
    }

    public IList<T> ReadToList<T>()
    {
        return MapToList<T>(_reader);
    }

    public Task<bool> NextResultAsync()
    {
        return _reader.NextResultAsync();
    }

    public Task<bool> NextResultAsync(CancellationToken ct)
    {
        return _reader.NextResultAsync(ct);
    }

    public bool NextResult()
    {
        return _reader.NextResult();
    }
}

4. Case 1.Store Proc returing single data table.

T obj;
await dbContext.StoreProcedure($"<store procedure name")
               .ExecuteAsync((handler) =>
                  {
                      obj = handler.ReadToList<T>().ToList();
                  });

5. Map Data Reader to List<T>.

6. Case 2: Store Proc returing multiple data tables.

T1 Obj1;
T2 Obj2;

await dbContext.StoreProcedure($"<store procedure name")
      .ExecuteAsync((handler) =>
                 {
                     obj1 = handler.ReadToList<T1>().ToList();
                     if (handler.NextResult())
                     {
                         obj2 = handler.ReadToList<T2>().ToList();
                     }
                 });

Try this and drop me your feedback.


Similar Articles