Reporting & BI Dashboard Data Sources — EF Dependency Injection, SqlDataSource Service Queries (v23.1)

In this post, I’ll summarize a set of enhancements introduced in v23.1 for SQL and Entity Framework data sources used in our Reporting and BI Dashboard products.

Resolve Entity Framework Core Context from the ASP.NET Core Dependency Injection Container

An ASP.NET Core application using Entity Framework supplies data to a report/dashboard as a DbContext object.

This object works in the scope of an HTTP request whose lifetime differs from those of a report/dashboard. A report is created in the HTTP request context and starts a background thread to obtain data and create a document. Because a report needs data after the initial HTTP request is completed and a dashboard uses cashed data source in a control, the default DbContext instance created by Entity Framework (in the scope of the HTTP request) cannot be used.

We now offer developers a way to resolve the appropriate Entity Framework Core context from the ASP.NET Core dependency injection container for dashboards/reports bound to an Entity Framework data source.

The following new API is used to create a custom service that returns a context object from the dependency injection container:

• IEFContextProvider
• IEFContextProviderFactory

The following code snippet implements a custom service that allows you to obtain the appropriate EF Core Context:

using DevExpress.Data.Entity;
using DevExpress.DataAccess.Web;
using System;
using Microsoft.Extensions.DependencyInjection;
namespace WebEFCoreApp.Services {
    public class CustomEFContextProviderFactory : IEFContextProviderFactory {
        private readonly IServiceProvider serviceProvider;
        public CustomEFContextProviderFactory(IServiceProvider serviceProvider) {
            this.serviceProvider = serviceProvider;
        }
        public IEFContextProvider Create() {
            return new CustomEFContextProvider(serviceProvider.CreateScope());
        }
    }
    public class CustomEFContextProvider : IEFContextProvider, IDisposable {
        private readonly IServiceScope scope;
        public CustomEFContextProvider(IServiceScope scope) {
            this.scope = scope;
        }

        public object GetContext(string connectionName, Type contextType) {
            // Returns the context for the specified `EFDataSource.ConnectionName`. 
            if (connectionName == "efCoreConnection")
                return scope.ServiceProvider.GetRequiredService(contextType);
            return null;
        }
        public void Dispose() {
            scope.Dispose();
        }
    }

Register the context and the factory implementation in the dependency injection container:

namespace DXWebApplication1 {
    public class Startup {
        public Startup(IConfiguration configuration, IWebHostEnvironment hostingEnvironment) {
            Configuration = configuration;
        }
        public void ConfigureServices(IServiceCollection services) {
            // ...
            services.ConfigureReportingServices(configurator => {
                configurator.ConfigureWebDocumentViewer(viewerConfigurator => {
                    // ...
                    viewerConfigurator.RegisterEFContextProviderFactory();
                });
                configurator.UseAsyncEngine();
            });
            services.AddDbContext(options => options.UseSqlite("Data Source=file:Data/nwind.db"), ServiceLifetime.Transient);
        }
    }
}

Review the following examples to learn more about our implementation:

• BI Dashboard for ASP.NET Core - Resolve Entity Framework Core Context from the DI Container
• Reporting for ASP.NET Core - Resolve the Entity Framework Core Context from the DI Container

Configure SqlDataSource Service Queries

Intercept SQL Operations and Commands

v23.1 ships with new IDBCommandInterceptor and IDBConnectionInterceptor interfaces.

These interfaces allow you to intercept, modify, and/or suppress SQL operations and commands while establishing a connection to a database. The list includes low-level database operations such as executing a command or setting a key-value pair in a session context. Once the connection is open, you can store values in the session context and execute a desired request.

The following examples address specific usage scenarios:

• BI Dashboard for ASP NET.Core - Row-Level Security
• Reporting for ASP NET.Core - Row-Level Security
• Reporting for WinForms - Override the Default Isolation Level

Set Isolation Levels

We also added ConnectionOptions.IsolationLevel and SqlQuery.IsolationLevel properties to help specify the isolation level used to isolate one transaction from another.

You can set IsolationLevel to one of the following values:

• None
• ReadUncommitted
• ReadCommitted
• RepeatableRead
• Serializable
• Snapshot

Each time a query is executed, a corresponding transaction type will be opened (except for the None). Once the request is executed, the transaction is immediately closed.

The benefits of this strategy are as follows:

• Set Isolation Level in the UI via the property grid dialog. You do not need to create a stored procedure to set the transaction level or write any code.
  
• Execute queries without SQL database locking. This can significantly reduce query times.

What’s New in v23.1

Refer to the following web page to learn more about our most recent release (and rate the release by voting in our online poll): Our Newest Features (v23.1).

Free DevExpress Products - Get Your Copy Today