StoredProcedureEFCore 1.0.5
Entity Framework Core extension to execute stored procedures and map the result to a custom model
Install-Package StoredProcedureEFCore -Version 1.0.5
dotnet add package StoredProcedureEFCore --version 1.0.5
<PackageReference Include="StoredProcedureEFCore" Version="1.0.5" />
paket add StoredProcedureEFCore --version 1.0.5
Execute stored procedures with Entity Framework Core
DbContext extension with LoadStoredProc method which creates
an IStoredProcBuilder
to build a stored procedure with a custom
mapping strategy using the provided DbDataReader
extension.
The method handles :
- Extra column in result set
- Extra property in model
- Null values in result set
- Underscores in result set column names ("column_name" is mapped to ColumnName property)
- Int (db) to enumeration (result model) mapping
Example
List<Model> rows = null;
ctx.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 300L)
.AddParam("limitOut", out IOutParam<long> limitOut)
.Exec(r => rows = r.ToList<Model>());
long limitOutValue = limitOut.Value;
ctx.LoadStoredProc("dbo.ReturnBoolean")
.AddParam("boolean_to_return", true)
.ReturnValue(out IOutParam<bool> retParam)
.ExecNonQuery();
bool b = retParam.Value;
ctx.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 1L)
.ExecScalar(out long l);
API
DbContext
IStoredProcBuilder LoadStoredProc(string name)
IStoredProcBuilder
IStoredProcBuilder AddParam<T>(string name, T val); // Input parameter
IStoredProcBuilder AddParam<T>(string name, T val, out IOutParam<T> outParam, int size, byte precision, byte scale); // Input/Ouput parameter
IStoredProcBuilder AddParam<T>(string name, out IOutParam<T> outParam, int size, byte precision, byte scale); // Ouput parameter
IStoredProcBuilder ReturnValue<T>(out IOutParam<T> retParam, int size, byte precision, byte scale);
IStoredProcBuilder SetTimeout(int timeout);
Task ExecAsync(Func<DbDataReader, Task> action, CancellationToken cancellationToken);
Task<int> ExecNonQueryAsync(CancellationToken cancellationToken);
Task ExecScalarAsync<T>(Action<T> action, CancellationToken cancellationToken);
DbDataReader
Task<List<T>> ToListAsync<T>();
Task<Dictionary<TKey, TValue>> ToDictionaryAsync<TKey, TValue>(Func<TValue, TKey> keyProjection);
Task<Dictionary<TKey, List<TValue>>> ToLookupAsync<TKey, TValue>(Func<TValue, TKey> keyProjection);
Task<HashSet<T>> ToSetAsync<T>();
Task<List<T>> ColumnAsync<T>();
Task<List<T>> ColumnAsync<T>(string columnName);
Task<T> FirstAsync<T>();
Task<T> FirstOrDefaultAsync<T>();
Task<T> SingleAsync<T>();
Task<T> SingleOrDefaultAsync<T>();
All these methods have a corresponding async method : ToListAsync, ToDictionaryAsync, ...
Installation
Install-Package StoredProcedureEFCore
Why ?
Stored procedure execution was previously not supported in Entity Framework Core:
- Raw store access APIs: Support for ad hoc mapping of arbitrary types #1862
- Stored procedure mapping support #245
It is now supported since EF Core 2.1 but this library has few advantages compared to FromSql:
- Extra property in the model won't throw an exception. The property keeps its default value
- The interface is easier to use. Output parameters and return values seem difficult to use with EF Core
- Mapping is 30% faster
Execute stored procedures with Entity Framework Core
DbContext extension with LoadStoredProc method which creates
an IStoredProcBuilder
to build a stored procedure with a custom
mapping strategy using the provided DbDataReader
extension.
The method handles :
- Extra column in result set
- Extra property in model
- Null values in result set
- Underscores in result set column names ("column_name" is mapped to ColumnName property)
- Int (db) to enumeration (result model) mapping
Example
List<Model> rows = null;
ctx.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 300L)
.AddParam("limitOut", out IOutParam<long> limitOut)
.Exec(r => rows = r.ToList<Model>());
long limitOutValue = limitOut.Value;
ctx.LoadStoredProc("dbo.ReturnBoolean")
.AddParam("boolean_to_return", true)
.ReturnValue(out IOutParam<bool> retParam)
.ExecNonQuery();
bool b = retParam.Value;
ctx.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 1L)
.ExecScalar(out long l);
API
DbContext
IStoredProcBuilder LoadStoredProc(string name)
IStoredProcBuilder
IStoredProcBuilder AddParam<T>(string name, T val); // Input parameter
IStoredProcBuilder AddParam<T>(string name, T val, out IOutParam<T> outParam, int size, byte precision, byte scale); // Input/Ouput parameter
IStoredProcBuilder AddParam<T>(string name, out IOutParam<T> outParam, int size, byte precision, byte scale); // Ouput parameter
IStoredProcBuilder ReturnValue<T>(out IOutParam<T> retParam, int size, byte precision, byte scale);
IStoredProcBuilder SetTimeout(int timeout);
Task ExecAsync(Func<DbDataReader, Task> action, CancellationToken cancellationToken);
Task<int> ExecNonQueryAsync(CancellationToken cancellationToken);
Task ExecScalarAsync<T>(Action<T> action, CancellationToken cancellationToken);
DbDataReader
Task<List<T>> ToListAsync<T>();
Task<Dictionary<TKey, TValue>> ToDictionaryAsync<TKey, TValue>(Func<TValue, TKey> keyProjection);
Task<Dictionary<TKey, List<TValue>>> ToLookupAsync<TKey, TValue>(Func<TValue, TKey> keyProjection);
Task<HashSet<T>> ToSetAsync<T>();
Task<List<T>> ColumnAsync<T>();
Task<List<T>> ColumnAsync<T>(string columnName);
Task<T> FirstAsync<T>();
Task<T> FirstOrDefaultAsync<T>();
Task<T> SingleAsync<T>();
Task<T> SingleOrDefaultAsync<T>();
All these methods have a corresponding async method : ToListAsync, ToDictionaryAsync, ...
Installation
Install-Package StoredProcedureEFCore
Why ?
Stored procedure execution was previously not supported in Entity Framework Core:
- Raw store access APIs: Support for ad hoc mapping of arbitrary types #1862
- Stored procedure mapping support #245
It is now supported since EF Core 2.1 but this library has few advantages compared to FromSql:
- Extra property in the model won't throw an exception. The property keeps its default value
- The interface is easier to use. Output parameters and return values seem difficult to use with EF Core
- Mapping is 30% faster
Dependencies
-
.NETStandard 2.0
- Microsoft.EntityFrameworkCore.Design (>= 2.2.0)
- System.Data.Common (>= 4.3.0)
Used By
NuGet packages (1)
Showing the top 1 NuGet packages that depend on StoredProcedureEFCore:
Package | Downloads |
---|---|
DevDiamond.AspNetCore.JqueryDataTable
Package Description
|
GitHub repositories
This package is not used by any popular GitHub repositories.
Version History
Version | Downloads | Last updated | |
---|---|---|---|
1.0.5 | 1,795 | 11/15/2020 | |
1.0.4 | 24,622 | 6/16/2020 | |
1.0.3 | 146 | 6/15/2020 | |
1.0.2 | 7,476 | 4/10/2020 | |
1.0.1 | 8,385 | 2/13/2020 | |
1.0.0 | 2,442 | 1/14/2020 | |
0.3.15 | 7,454 | 10/30/2019 | |
0.3.14 | 542 | 10/22/2019 | |
0.3.13 | 299 | 10/17/2019 | |
0.3.12 | 316 | 8/9/2019 | |
0.3.11 | 10,770 | 6/1/2019 | |
0.3.10 | 14,313 | 11/15/2018 | |
0.3.9 | 6,941 | 5/4/2018 | |
0.3.8 | 836 | 4/21/2018 | |
0.3.7 | 703 | 4/3/2018 | |
0.3.6 | 707 | 3/16/2018 | |
0.3.5 | 466 | 3/16/2018 | |
0.3.4 | 727 | 3/2/2018 | |
0.3.3 | 787 | 2/1/2018 | |
0.3.2 | 587 | 1/19/2018 | |
0.3.1 | 517 | 1/8/2018 | |
0.3.0 | 505 | 1/8/2018 | |
0.2.0 | 594 | 12/29/2017 | |
0.1.1 | 556 | 12/28/2017 | |
0.1.0 | 451 | 12/26/2017 |