snickler/efcore-fluentstoredprocedure

EFCore Extension that allows a means to map a stored procedure to a class, fluently.

Snickler.EFCore

Fluent Methods for mapping Stored Procedure results to objects in EntityFrameworkCore

Usage

Executing A Stored Procedure

Add the using statement to pull in the extension method. E.g: using Snickler.EFCore

      var dbContext = GetDbContext();
      dbContext.LoadStoredProc("dbo.SomeSproc")
               .WithSqlParam("fooId", 1)              
               .ExecuteStoredProc((handler) =>
                {                  
                    var fooResults = handler.ReadToList<FooDto>();      
                    // do something with your results.
                });

Handling Multiple Result Sets

      var dbContext = GetDbContext();
      dbContext.LoadStoredProc("dbo.SomeSproc")
               .WithSqlParam("fooId", 1)              
               .ExecuteStoredProc((handler) =>
                {                  
                    var fooResults = handler.ReadToList<FooDto>();      
                    handler.NextResult();
                    var barResults = handler.ReadToList<BarDto>();
                    handler.NextResult();
                    var bazResults = handler.ReadToList<BazDto>()
                });

Handling Output Parameters

      DbParameter outputParam = null;
    
      var dbContext = GetDbContext();
      dbContext.LoadStoredProc("dbo.SomeSproc")
               .WithSqlParam("fooId", 1)  
               .WithSqlParam("myOutputParam", (dbParam) =>
               {                 
                 dbParam.Direction = System.Data.ParameterDirection.Output;
                 dbParam.DbType = System.Data.DbType.Int32;          
                 outputParam = dbParam;
               })
               .ExecuteStoredProc((handler) =>
                {                  
                    var fooResults = handler.ReadToList<FooDto>();      
                    handler.NextResult();
                    var barResults = handler.ReadToList<BarDto>();
                    handler.NextResult();
                    var bazResults = handler.ReadToList<BazDto>()
                });
                
                int outputParamValue = (int)outputParam?.Value;

Using output parameters without returning a result set

      DbParameter outputParam = null;

      var dbContext = GetDbContext();

      await dbContext.LoadStoredProc("dbo.SomeSproc")
            .WithSqlParam("InputParam1", 1)
            .WithSqlParam("myOutputParam", (dbParam) =>
            {
                  dbParam.Direction = System.Data.ParameterDirection.Output;
                  dbParam.DbType = System.Data.DbType.Int16;
                  outputParam = dbParam;
            })

            .ExecuteStoredNonQueryAsync();

      int outputParamValue = (short)outputParam.Value;

Using output parameters without returning a result set but also getting the number of rows affected

Make sure your stored procedure does not contain SET NOCOUNT ON.

      int numberOfRowsAffected = -1;

      DbParameter outputParam = null;

      var dbContext = GetDbContext();

      numberOfRowsAffected = await dbContext.LoadStoredProc("dbo.SomeSproc")
            .WithSqlParam("InputParam1", 1)
            .WithSqlParam("myOutputParam", (dbParam) =>
            {
                  dbParam.Direction = System.Data.ParameterDirection.Output;
                  dbParam.DbType = System.Data.DbType.Int16;
                  outputParam = dbParam;
            })

            .ExecuteStoredNonQueryAsync();

      int outputParamValue = (short)outputParam.Value;

Changing the execution timeout when waiting for a stored procedure to return

      DbParameter outputParam = null;

      var dbContext = GetDbContext();

      // change timeout from 30 seconds to 300 seconds (5 minutes)
      await dbContext.LoadStoredProc("dbo.SomeSproc", commandTimeout:300)
            .WithSqlParam("InputParam1", 1)
            .WithSqlParam("myOutputParam", (dbParam) =>
            {
                  dbParam.Direction = System.Data.ParameterDirection.Output;
                  dbParam.DbType = System.Data.DbType.Int16;
                  outputParam = dbParam;
            })

            .ExecuteStoredNonQueryAsync();

      int outputParamValue = (short)outputParam.Value;

efcore-fluentstoredprocedure's Issues

UnitTests - LoadStoredProc

Hello,

Is there any way we can do unit tests for LoadStoredProc? Or is it in plan the near future?

Regards

Should work without EFCore DbContext - on DbConnection.

Should work without EFCore DbContext - on DbConnection.

Update to EF Core 2.1

Hi,

Can you update project to support EF Core 2.1?

Thanks

run multiple stored procedure with single transaction

Hierarchies using EFCore-FluentStoredProcedure

public class Parent
{
    public Guid Id { get; set; }
    public IEnumerable<Child> Children { get; set; }
}
public class Child
{
    public Guid ParentId { get; set; }
}

I have a parent menu and child menus. Using `EFCore-FluentStoredProcedure` How do i produce the following?

```yaml
{
  "Hospitals": [
    {
      "37 Military": {
        "Apps": [
          {
            "Name": "Global",
            "Menu-0": [
              {
                "Name": "General",
                "Menu-1": [
                  {
                    "Name": "Staff Attandance",
                    "Menu-2": [
                      {
                        "Name": "Clock-In"
                      },
                      {
                        "Name": "Clock-Out"
                      }
                    ]
                  },
                  {
                    "Name": "Pesonal Account",
                    "Menu-2": [
                      {
                        "Name": "View",
                        "Menu-3": [
                          {
                            "Name": "Hours Worked"
                          },
                          {
                            "Name": "Lateness Report"
                          },
                          {
                            "Name": "Overtime"
                          },
                          {
                            "Name": "My PaySlip"
                          },
                          {
                            "Name": "E-Notice Board"
                          },
                          {
                            "Name": "My Meno"
                          },
                          {
                            "Name": "My Voucher Request Status"
                          },
                          {
                            "Name": "My PLT Request Status"
                          },
                          {
                            "Name": "My Stock Request Status"
                          }
                        ]
                      },
                      {
                        "Name": "Request",
                        "Menu-3": [
                          {
                            "Name": "Request for Petty Cash"
                          },
                          {
                            "Name": "Request from General Store"
                          },
                          {
                            "Name": "Request for PLT"
                          },
                          {
                            "Name": "Request for Salary Advance"
                          }
                        ]
                      },
                      {
                        "Name": "Send",
                        "Menu-3": [
                          {
                            "Name": "Send Memo"
                          },
                          {
                            "Name": "Send Suggestion Box"
                          }
                        ]
                      }
                    ]
                  },
                  {
                    "Name": "Theme",
                    "Menu-2": [
                      {
                        "Name": "Default"
                      },
                      {
                        "Name": "Dark"
                      },
                      {
                        "Name": "Blue"
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      },
      "Zion Hospital":{
      }
    }
  ]
}

proper use

Handle enum's

MapToList fails when coming accross enum properties.

Could handle them something like:

    var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
    if(prop.PropertyType.IsEnum)
    {
             prop.SetValue(obj, val == DBNull.Value ? null : Enum.ToObject(prop.PropertyType, (int)val));
     }
     else
     {
              prop.SetValue(obj, val == DBNull.Value ? null : val);
      }

The above isn't really finished, as it doesn't handle null correcty,- i.e should map DbNull only to nullable enums.

Nuget package release?

Consider releasing as a nuget package

NuGet Support??

Am I correct that there is no nuget support for this?

If you want people to use this, you really need nuget. You could probably have it done in an hour, and far more people will be able to use this.

Including me. I don't want to take any references that don't have nuget packages.

Consider ditching Activator.CreateInstance

You could ditch Activator.CreateInstance for a public default constructor constraint on type T. i.e:

            private IList<T> MapToList<T>(DbDataReader dr) where T: new()
            {
                var objList = new List<T>();
                var props = typeof(T).GetRuntimeProperties();

                var colMapping = dr.GetColumnSchema()
                    .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
                    .ToDictionary(key => key.ColumnName.ToLower());

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        T obj = new T();
                        foreach (var prop in props)
                        {
                            var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
                            prop.SetValue(obj, val == DBNull.Value ? null : val);
                        }
                        objList.Add(obj);
                    }
                }
                return objList;
            }

You could also cache the props for type T, to save calling GetRuntimeProperties on every call you could grab them from the cache if they are there. Not sure how much that would save as I haven't checked to see if GetRuntimeProperties does caching of it's own.

Doesn't work properly with EF Core 3.1

Due to an issue in code with define directives, a single line workaround to make this work seamlessly with EF Core 3.0 breaks on 3.1

System.TypeLoadException when trying to Load Stored Procedure with Output parameters and without Resultset

My EFCore Version is v3.1.4 and my Snickler EFCore Version is v1.1.0.

I want to execute the following Code:
...
DbParameter outputParam1 = null;
DbParameter outputParam2 = null;
...
context.LoadStoredProc("ProcedureName")
.WithSqlParam("@id", ID)
.WithSqlParam("@param1", (dbParam) =>
{
dbParam.Direction = ParameterDirection.Output;
dbParam.DbType = DbType.Int32;
outputParam1 = dbParam;
})
.WithSqlParam("@Param2", (dbParam) =>
{
dbParam.Direction = ParameterDirection.Output;
dbParam.DbType = DbType.Int32;
outputParam2 = dbParam;
}).ExecuteStoredNonQuery();

And get this Error:
System.TypeLoadException: "The Type "Microsoft.EntityFrameworkCore.RelationalMetadataExtensions" in the Assembly "Microsoft.EntityFrameworkCore.Relational, Version=3.1.4.0, Culture=neutral, PublicKeyToken=adb9793829ddae60" could not be loaded."

Should I use Different Version of EFCore or Snickler Library to fix this issue?

[Bug] Connection timeout when calling handler.NextResultAsync

Hi,
I got Connection timeout exception when calling NextResultAsync inside ExecuteStoredProcAsync
I think the bug because Action handleResults, it should be Func<SprocResults, Task> handleResults and you need to await it to avoid the issue,

Thanks.

System.NotSupportedException: 'Specified method is not supported.'

I am using this library with a .NET 4.7.1 app and I get System.NotSupportedException: 'Specified method is not supported.' when I call dr.GetColumnSchema();

Remap stored procedure output column names

Is there a way to specify column names from the output? I.e. my SP returns column "aaa" but it maps to property "bbb" in my class. In similar frameworks I've seen this handled by tagging the property with an attribute like [StoredProcedure.Name("aaa")].

Rows Affected

First off, love this project, thanks for putting it out there.

Here's my question: is there a way to get access to rows affected?

I'm using this in a situation where the sproc is just doing some inserts, but no rows are returned. In this case, SQL Server returns the number of rows affected, but so far I don't see a way to get access to that. It seems like it would be a property on handler:

var result = 0;

db.LoadStoredProc("dbo.sproc_ImportUsers")
.WithSqlParam("...", "...")
...
.ExecuteStoredProc(handler => { result = handler.RowsAffected });

... Or, perhaps even better, just have ExecuteStoredProc return an int and have an overload that doesn't require the handler parameter:

return db.LoadStoredProc("...").ExecuteStoredProc();

Thanks!

New nuget?

Hi there,

Any chance for a new nuget, to get 820af34 on board?

Support EF Core 3.x

Doesn't work with ApiAuthorizationDbContext

Did someone find a solution?

Not working with Postgres

When using with a postgres sql database a 'not supported' exception is thrown by the call to GetColumnSchema.

var colMapping = dr.GetColumnSchema()
                    .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
                    .ToDictionary(key => key.ColumnName.ToLower());

Names of parameters should be not obligated - order is enough

Names of parameters should be not obligated - order is enough

Then this can be used to replace Microsoft.ApplicationBlocks.Data

mapping sql statements to FluentStoredProcedure

Hi, I have set of sql statements in a table, any chance I get an overload to hand them off to FSP to execute.

thanks

Support conversions from db type to string properties

Suppose your POCO object has a string property, but the value returned by the stored procedure is a different type, like int. At the moment this throws an exception saying int cannot be converted to string property. Would be good to handle the conversion to string automatically if there is isn't a "loss" of information caused by the conversion (other than type information).

Critical problems moving to NET6

After migrating to NET6, this code results in:

 System.MissingMethodException: Method not found: 'System.Object Microsoft.EntityFrameworkCore.Infrastructure.IAnnotatable.get_Item(System.String)'. at Snickler.EFCore.EFExtensions.LoadStoredProc(DbContext context, String storedProcName, Boolean prependDefaultSchema, Int16 commandTimeout) at Keystone.Data.Sprocs.Sproc_ProgressStats(KeystoneDb db, Int32 projectSubjectId) in D:\Dev\Kinetiq\Keystone\Dev\Keystone.Data\Sprocs.cs:line 

Here's the code:

    public static List<AllProgressStatsDTO> Sproc_AllProgressStats(this KeystoneDb db, int userId)
    {
        IList<AllProgressStatsDTO> result = new List<AllProgressStatsDTO>();

        db.LoadStoredProc("dbo.sproc_AllProgressStats")
            .WithSqlParam("UserID", userId)
            .ExecuteStoredProc(handler =>
            {
                result = handler.ReadToList<AllProgressStatsDTO>();
            });

        return result.ToList();
    }

Any thoughts? This is blocking my team today.

Multiple result sets.

Does This Support Table Type Parameter (TVP)?

I need to use some procs that use TVP so will need to pass a table. Can't find a way to do this with snickler so looking for some help if any. Thanks. I know this hasn't been updated in a while so is development stopped? Would appreciate any recommendations for a package that does support TVP.

Is there an option to pass Collection of SqlParameters?

We would like to use the component inside a generic function that will receive a list of parameters and will use them while calling LoadStoredProc.

Handle output parameters.