#entity-framework-core

Continue the tutorial about APIs with Entity Framework Core and in particular how to implemente the #PUT to update a record with dependencies. #efcore #minimalapi #csharp

Continuing the topic, I want to give a complete example of minimal APIs in Blazor with Entity Framework Core with complex objects.I always struggle to have a solution working when my model has dependencies with other object. Here I show my test and my code. The code is in NET9. In the Microsoft documentation, there are some examples, but it is not complex enough. A few days ago, I posted about…

Here how to change the minimal #APIs with Entity Framework Core for #POST and #GET in order to save and retrieve an object with dependencies #efcore #blazor #aspnetcore

Continuing the topic, I want to give a complete example of minimal APIs in Blazor with Entity Framework Core with complex objects.I always struggle to have a solution working when my model has dependencies with other object. Here I show my test and my code. The code is in NET9. In the Microsoft documentation, there are some examples, but it is not complex enough. A few days ago, I posted about…

I want to give a complete example of minimal #APIs in #Blazor with Entity Framework Core with #complex #objects. #efcore

In this new post, I want to give a complete example of minimal APIs in Blazor with Entity Framework Core with complex objects. I always struggle to have a solution working when my model has dependencies with other object. Here I show my test and my code. The code is in NET9. In the Microsoft documentation, there is some example but it is not complex enough. The full source code of this post is…

In this post, we will see how to implement database connection resiliency in Entity Framework Core, an update to my previous post. Here, I’m using an execution strategy for Database operation define using Transaction scope.

As you know, we already looked at the inbuilt and custom strategy execution pattern using EnableRetry and using a Custom execution strategy.

So, if interested to see those option, please visit this other post

Database Connection Resiliency Using EnableRetry and Execution Strategy

Limitation of Retry and Default Execution Strategies

As we understood in our previous articles, RetryOnFailure and custom execution strategies patterns are very useful for retrying defaulted commands automatically in Entity Framework Core.

In other words, these patterns help in overcoming Transient errors/faults caused due to network connectivity issues or the temporary unavailability of a service, or timeouts issues, etc.

So, when an issue or transient error occurs, resiliency retries all the operations by re-establishing the connection.

Limitations of Default Execution Strategies

Retry /Default Execution Strategies automatically retries all operations on failures.

Retries all operations without considering data and its state verification managed through operations.

Default Execution strategies retry all operations defined in the Transaction.

Any database operation using Transaction scope like Ambient transaction could lead to data corruption.

In such scenarios, one can execute the execution strategy using delegate which shall create the scope of all the database operation that needs to be performed, etc.

Execution Pattern for Transaction

Now, we can update our example of database connection resiliency in Entity Framework Core with transaction as in the following code:

public SaveInvoiceResponse SaveInvoice(Invoice invoice) { SaveInvoiceResponse rtn = new SaveInvoiceResponse(); // step 1 var strategy = _context.Database.CreateExecutionStrategy(); strategy.Execute(() => { // step 2 using (var transaction = _context.Database.BeginTransaction()) { try { // step 3 _context.Invoice.Add(invoice); var result = _context.SaveChanges(); // step 4 transaction.Commit(); rtn.Success = true; rtn.InvoiceId = invoice.Id; } catch (Exception ex) { _log?.LogError(ex, "Save Invoice Error"); transaction.Rollback(); rtn.ErrorMessage = ex.Message; } } }); return rtn; }

Declare the execution strategy

Createa a new Transaction scope for a given context (one can use multi DBContext within given Transcope if needed

Define Database resource updates if any

Commit the transaction (for one or multiple DBContext)

In conclusion, the above sample uses the same database schema and scaffolding model entities which we learned in our Creating a Model for an Existing Database article.

Transaction State verification

In addition, Entity Framework Code provides an extension method ExecuteInTransaction() to perform state verification of any database operation.

So, this API I found to be very useful to keep connection resiliency and at the same time keeping track of if the database operation is successful or not.

using(var dbcontext = new EmployeeContext()) { var strategy = dbcontext.Database.CreateExecutionStrategy(); dbcontext.EmployeeDb.Add(new EmployeeDb { FirstName = "Enrico", LastName = "PSC", Id = "1234" }); strategy.ExecuteInTransaction(dbcontext, operation: context = >{ context.SaveChanges(acceptAllChangesOnSuccess: false); }, verifySucceeded: context = >context.EmployeeDb.AsNoTracking().Any(b = >b.Id == "1234")); dbcontext.ChangeTracker.AcceptAllChanges(); }

In the above code, verifySucceeded is the delegate, that tests whether the operation succeeded even if the exception is thrown while the Transaction was being committed.

Read more…

In conclusion, I published other posts about Entity Framework Core that may be interesting to you:

Transactions with Entity Framework Core

Entity Framework Core and calling a stored procedure

Creating a Model for an Existing Database

Database Connection Resiliency in Entity Framework Core

The post Database Connection Resiliency in Entity Framework Core: update appeared first on PureSourceCode.

#Database #Connection #Resiliency in #Entity #Framework #Core: update

In this post, we will see how to implement database connection resiliency in Entity Framework Core, an update to my previous post. Here, I’m using an execution strategy for Database operation define using Transaction scope.

As you know, we already looked at the inbuilt and custom strategy execution pattern using EnableRetry and using a Custom execution strategy.

So, if interested to see…

In this post, we will see how to implement database connection resiliency using an inbuilt or custom execution strategy using Entity Framework Core. The connection resiliency feature retries a defaulted database commands automatically.

Applications in the cloud environment are more sensitive to transient faults. So, transient faults are generally temporary in nature and often get corrected on its own. Network connectivity issues, the temporary unavailability of a service or timeouts issues may caused errors in your applications.

For starting, connection resiliency is implemented by supplying an Execution Strategy. In other words, this Execution strategy encapsulates the logic necessary to detect failures and retry failed commands. You can find some details on Microsoft documentation.

Importantly, this resiliency feature can be used with any database like SQL, MySQL, Oracle, SQL Azure DB and more. Each database provider can supply execution strategies for failure conditions and retry policies to address the needs.

In short in this article we will see both basic and custom execution techniques to implement resiliency in SQL database connections created using Entity Framework Core provider.

Resiliency Using Execution Strategies

For instance, using default or custom Execution strategies, we can implement an efficient resiliency in Entity Framework Core. To clarify, an execution strategy can be defined per database Context basis by configuring the DbContextOptionsBuilder objects.

Default Execution strategy -Enable Retry Pattern

Firstly, update the ConfigureServices() for EnableRetryOnFailure to enable default retrying execution strategy. So, you can configure DbContextOptionsBuilder within Startup.cs as below

services.AddDbContext<EmployeeContext>(options => { options.UseSqlServer(Configuration.GetConnectionString("EmployeeDB"), sqlServerOptionsAction: sqlOptions => { sqlOptions.EnableRetryOnFailure(); }); });

In addition, you can set the EnableRetry option using the OnConfiguring() method also as below.

builder.Services.AddDbContext<BillingContext>(options => options.UseSqlServer(configuration.GetConnectionString("BillingDatabase"), providerOptions => providerOptions.EnableRetryOnFailure() ));

Additionally, one can use EnableRetryOnFailure() overloaded method for specifying the option like Maximum retry count or Maximum retry delay

builder.Services.AddDbContext<BillingContext>(options => options.UseSqlServer(configuration.GetConnectionString("BillingDatabase"), providerOptions => providerOptions.EnableRetryOnFailure( maxRetryCount: 10, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null ) ));

Custom Execution strategy

So, if you wish to register a custom execution strategy then please see below code example on achieving that.

Firstly, update the ConfigureServices() within Startup.cs as below for using any CustomExecutionStrategy().

public void ConfigureServices(IServiceCollection services) { services.AddControllers(); services.AddDbContext < EmployeeContext > (options = >{ options.UseSqlServer(Configuration.GetConnectionString("EmployeeDB"), sqlServerOptionsAction: sqlOptions = >{ sqlOptions.ExecutionStrategy(c = >new CustomExecutionStrategy(c)); }); }); }

Another option is to update the OnConfiguring() as below for using any CustomExecutionStrategy()

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseSqlServer("Server=localhost\\SQLEXPRESS;Database=master;Trusted_Connection=True;", builder = >builder.ExecutionStrategy(c = >new CustomExecutionStrategy(c))); } }

How to define CustomExecutionStrategy

The custom strategy class derived using class ExecutionStrategy. You have an example below

public class CustomExecutionStrategy: ExecutionStrategy { public CustomExecutionStrategy(DbContext context) : base(context, ExecutionStrategy.DefaultMaxRetryCount, ExecutionStrategy.DefaultMaxDelay) { } public CustomExecutionStrategy(ExecutionStrategyDependencies dependencies) : base(dependencies, ExecutionStrategy.DefaultMaxRetryCount, ExecutionStrategy.DefaultMaxDelay) { } public CustomExecutionStrategy(DbContext context, int maxRetryCount, TimeSpan maxRetryDelay) : base(context, maxRetryCount, maxRetryDelay) { } protected override bool ShouldRetryOn(Exception exception) { return exception.GetType() == typeof(DbUpdateException); } }

Read more…

In conclusion, I published other posts about Entity Framework Core that may be interesting to you:

Transactions with Entity Framework Core

Entity Framework Core and calling a stored procedure

Creating a Model for an Existing Database

The post Database Connection Resiliency in Entity Framework Core appeared first on PureSourceCode.

#Database #Connection #Resiliency in #Entity #Framework #Core

In this post, we will see how to implement database connection resiliency using an inbuilt or custom execution strategy using Entity Framework Core. The connection resiliency feature retries a defaulted database commands automatically.

Applications in the cloud environment are more sensitive to transient faults. So, transient faults are generally temporary in nature and often get corrected on…

In the last couple of weeks, I was talking about Entity Framework Core (see Creating a model for an existing database or Calling Stored Procedures) but today the topic is Transactions.

In the SQL world, it is common to use Transactions to save or update records or bunch of records in a database. Using transactions allows you to implement or increment the resilient of your application. You have the documentation on the Microsoft site.

If you read the documentation, adding transactions is very straight forward and in the Microsoft documentation there are a lot of examples. The following example is the common one.

using (var context = new BloggingContext()) { using (var transaction = context.Database.BeginTransaction()) { try { context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" }); context.SaveChanges(); context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" }); context.SaveChanges(); var blogs = context.Blogs .OrderBy(b => b.Url) .ToList(); // Commit transaction if all commands succeed, transaction will auto-rollback // when disposed if either commands fails transaction.Commit(); } catch (Exception) { // TODO: Handle failure } } }

After that, there is only one cryptical error that Entity Framework gives you at runtime:

InvalidOperationException: The configured execution strategy ‘SqlServerRetryingExecutionStrategy’ does not support user initiated transactions. Use the execution strategy returned by ‘DbContext.Database.CreateExecutionStrategy()’ to execute all the operations in the transaction as a retriable unit.

Therefore, this issue is more visible while performing Entity Framework Core operation in a shared environment like cloud.

Resolution

Generally speaking, a common cause for having this error are:

transient errors

network issues

hardware issues

In other words, this error also indicates that if the application is configured with Default Execution strategies like RetryonFailure Pattern or Custom execution strategies are not sufficient to overcome the issue.

So, using Transactions with BeginTransaction() requires to invoke the Execution strategy. In addition, such execution strategy should encapsulate everything that needs to be executed.

Execution Pattern for Transaction

First of all, we have to define the Execution strategy using CreateExecutionStrategy() with a delegate encapsulating all database operation.

using(var db = new EmployeeContext()) { var strategy = db.Database.CreateExecutionStrategy(); strategy.Execute(() = >{ using(var context = new EmployeeContext()) { //BeginTransaction using(var transaction = context.Database.BeginTransaction()) { //First Update context.EmployeeDb.Add(new EmployeeDb { FirstName = "Enrico", LastName = "PSC" }); context.SaveChanges(); //SecondUpdate context.EmployeeDb.Add(new EmployeeDb { FirstName = "Enrico2", LastName = "PSC" }); context.SaveChanges(); //End Transaction transaction.Commit(); } } }); }

Configure Context options

Now, to use transactions with Entity Framework Core properly, we have to add a bit more configuration in the context. For example, check in your Startup.cs if you have this setting:

builder.Services.AddDbContext<BillingContext>(options => options.UseSqlServer(configuration.GetConnectionString("BillingDatabase"), providerOptions => providerOptions.EnableRetryOnFailure( maxRetryCount: 10, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null ) ));

The post Transactions with Entity Framework Core appeared first on PureSourceCode.