verdie-g / storedprocedureefcore Goto Github PK

Since one PropertiesCache is created for each type T it is useless to add T in the computed hash.

It looks like the most recent version of this codebase has the GetProperty() code commented out in the GetColumnsPropertyInfos() method which, at least in my case, resulted in nothing ever mapping. I fixed it locally, but you may want to address.

Find a way to create a key from a Type and a DbDataReader

When I call a store procedure with a geography column in EF Core i'm getting the error.

Model:
public Geometry Coordenates { get; set; }

g.db.LoadStoredProc("dbo.spGetCoordenates").AddParam("UsuarioId", c.Id).Exec(r => uDatoExtraModel = r.FirstOrDefault());

FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified. This library is for Net Framework.

I have a problem with executing a stored procedure, It works with a class but does not want to compile with generic type.
Here is my code

List<T> list = null;

_dbContext.LoadStoredProc($"dbo.{procedure_name}")
 .AddParam("max_rv", out IOutParam<byte[]> lastRVFromProcedure)
.Exec(r => list = r.ToList<T>());

And this is the error I get

Error CS1929 'DbDataReader' does not contain a definition for 'ToList' and the best extension method overload 'ParallelEnumerable.ToList(ParallelQuery)' requires a receiver of type 'ParallelQuery'

Edit

How do I use InOut params for the procedure?

I have the following scenario:

1. Open a DbTransaction
2. Perform updates (using SaveChanges() )
3. Run a stored procedure using LoadStoredProc
4. Perform more updates (again using SaveChanges() )
5. Commit the transaction

When I commit the transaction a get the error :This SqlTransaction has completed; it is no longer usable.

However I've tested the following:

Removing and not execute at all the SP from the above step 3 (and it works)
Replacing LoadStoredProc with ExecuteSqlRaw where I execute the SP using EF's API (and it works)

That's my SP dependent code:

this.LoadStoredProc("MySP")
.AddParam("LotID", lotId)
.Exec(reader => result = reader.ToList<Result>());

Using SQL Server - EF Core 3.0

Any insights?

As an example, it's impossible to call systems stored procedure to get range from a sequence:

        this.LoadStoredProc("sys.sp_sequence_get_range")
            .AddParam("sequence_name", sequence)
            .AddParam("range_size", range_size)
            .AddParam("range_first_value", out IOutParam<int> range_first_value)
            .AddParam("range_last_value", out IOutParam<int> range_last_value)
            .ExecNonQuery();

The current implementation of the async DbDataReader extension methods (such as ToListAsync), do not offer overloads with cancellation tokens.

This makes it impossible to control the lifetime of the underlying DbDataReader.ReadAsync and DbDataReader.IsDBNullAsync through an external cancellation token.

SELECT id, name FROM users and SELECT name, id FROM users coud both be mapped to the same type

class User
{
  public int Id { get; set; }
  public string Name { get; set; }
}

but the current implementation will use the same Prop array for both result sets.

Mapper<T>.PropertiesCache should be used to cache different order of columns. A second cache should be used to avoid generated several times the same Prop.

I've tried to pass null value as a value of the Stored Procedure parameter:
spBuild.AddParam(param.Name, (long?)null);

As a result this parameter is not passed into the Stored Procedure.
This issue was reproduced with SQLServer.

Hi,
I have the following suggestions.
Each and every operation/s the connection is creating/recreating, closing and disposing.
I assumes we only need to close the connection and not disposing it. The EF DB context should manage the dispose automatically, so we hence we can reuse the connection pool. Also check before opening and closing the connection if its already opened and closed respectively (Thread safe).

Regards,
Jose Clament

Steps to reproduce

• Create the follwing stored procedure:

CREATE PROCEDURE dbo.output_nullable_with_non_null_value
  @nullable INT = NULL OUTPUT
AS
BEGIN
    SET @nullable = 12
END

• Call the stored procedure using StoredProcedureEFCore and try accessing the value of the nullable out parameter:

await _db.LoadStoredProc("output_nullable_with_non_null_value")
    .AddParam("nullable", out IOutParam<int?> nullable)
    .ExecNonQueryAsync();

var val = nullable.Value;

Observed

The call to nullable.Value throws the follwing exception:

System.InvalidCastException : Invalid cast from 'System.Int32' to 'System.Nullable`1[[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]'

Expected

StoredProcedureEFCore should return a nullable out parameter whose value can be obtained including when the value is not NULL.

Hi, yes I know I can send SQL down through ExecuteSQLCommand. Your library is better, obviously. The source should compile with a change to .Net Standard 2.1 to be usable with core 3.1.

This is the price of doing something well: People want to continue using it!

Let me know if this is in the works, and thanks!

I'm using ASP.NET Boilerplate (ABP) for a project and would like to use StoredProcedureEFCore with it, but since ABP has its own system for managing transactions I want to be able to pass in the database transaction it holds for the database instead of StoredProcBuilder sniffing out a transaction for itself.

This line must set ParameterDirection.InputOutput rather then ParameterDirection.Output, because it is a in&out routine.

As a workaround we currently use this piece of code:
// fix missing input output parameter direction var type = builder.GetType().Assembly.GetTypes().Where(t => t.Name == "StoredProcBuilder").First(); var field = type.GetField("_cmd", BindingFlags.NonPublic | BindingFlags.Instance); var sqlCommand = field.GetValue(builder) as SqlCommand; sqlCommand.Parameters[parameterName].Direction = ParameterDirection.InputOutput;

Thx.

Can you have this target .NET Standard, so it can be used in class libraries?

I see IStoredProcBuilder.ExecNonQueryAsync (), etc. without CancellationToken parameter. Ability to cancel is often important.

This works great with only one parameter, but I can't figure out how to include more than one to work with the DbTool.cs

I'm currently trying to use this lib with NetTopologySuite to map geography columns.

Any help would be much appreciated.

Thanks!

I am trying to pass in a DataTable as a parameter to a stored procedure, and it's not working. I am getting a NotSupportedException.

After looking quickly, it appears to be because this is not supporting SQL Server specifically, which allows for a DataTable to be passed as a parameter with the SqlDbType.Structured.

Is there plan for future support of SQL Server specifically?

How can I make this function generic :

return Context.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 300L)
.AddParam("limitOut", out IOutParam limitOut)
.Exec(r => rows = r.ToListAsync());

The library does not .ConfigureAwait(false) on all internal await calls, thus it unnecessarily captures the synchronization context.

This may lead to deadlocks:
http://www.tugberkugurlu.com/archive/the-perfect-recipe-to-shoot-yourself-in-the-foot-ending-up-with-a-deadlock-using-the-c-sharp-5-0-asynchronous-language-features

Can u guide to how to Override the behavior of "Underscores in result set column names ("column_name" is mapped to ColumnName property)" Becoz my Model class already used in mobile & some another clients.

Hello,

So far I really like your library, but now I ran into an issue when using a transaction.

I have this method that call a couple of stored procedures that should all roll back when an error occurs.

protected async Task SaveOrder(OrderEntity order)
{
    using (var transaction = await this.Context.Database.BeginTransactionAsync())
    {
        await this.Context.LoadStoredProc("[dbo].[InsertOrder]")
                .AddParam("@OrderId", order.Id)
                .AddParam("@OrderDate", order.Date)
                .ExecNonQueryAsync();

        foreach(var line in order.Lines)
        {
            await this.Context.LoadStoredProc("[dbo].[InsertOrderLine]")
                .AddParam("@OrderId", order.Id)
                .AddParam("@LineNr", line.Id)
                .AddParam("@Quantity", line.Quantity)
                .ExecNonQueryAsync();
        }

        transaction.Commit();
    }
}

But when I run this it throws an exception:

System.InvalidOperationException : BeginExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.  The Transaction property of the command has not been initialized.

So now I'm looking for a way to set the transaction on the Command. Is it possible to do this?

The call of _cmd.ExecuteNonQuery and _cmd.ExecuteNonQueryAsync returns the number of rows affected, but the IStoredProcBuilder.ExecNonQuery and IStoredProcBuilder.ExecNonQueryAsync does not.

I am setting the command timeout on the database context as such:
dbContext.Database.SetCommandTimeout(3600);

However, your software is not honoring it. In the StoredProcBuilder constructor, you get a command by callling CreateCommand(). This apparently does not honor the timeout set above. Could you please add this code to set the timeout on the command based on the timeout on the context?

      // added for command timeout handlilng
      var commandTimeout = ctx.Database.GetCommandTimeout();
      if (commandTimeout.HasValue)
          cmd.CommandTimeout = commandTimeout.Value;

I am attaching a print screen illustrating the issue.

Thank you.

Hi! I have a model with a member declared as nullable enum (QuitMode) and the corresponding column in the database is declared as nullable int. When I call ToListAsync I'm getting the error:

Unable to cast object of type 'System.Int32' to type 'System.Nullable`1[Common.SharedTypes.SharedEnums.QuitMode]'.

Am I doing something wrong or is it a bug?

hello -

Your StoredProcedureEFCore works very well. But I had trouble when i updated my project to use EF core 3.0 and dotnet core 3.0.
Can you please update StoredProcedureEFCore for EF core 3.0 ? mainly the reference to Microsoft.EntityFrameworkCore.Design needs to be updated to the latest version I think.

Thanks!

I'm getting an error re string size when calling a proc that provides a string output parameter.
"Size property has an invalid size of 0"
I can't see how to set the output parameter size.
Regards
Derek