Row-Level Filtering in ASP.NET Core Reporting Application with SqlDataSource (Multi-Tenancy Support)

Files to look at

• UserService.cs
• SelectQueryFilterService.cs
• Startup.cs

This example demonstrates how to restrict access at the row level to the source data based on the user who is logged into the system. Create and register a service that implements the DevExpress.DataAccess.Web.ISelectQueryFilterService interface. The ISelectQueryFilterService.CustomizeFilterExpression method applies a conditional clause to the query passed to the method as a parameter. The Document Viewer, Report Designer's Preview, and Query Builder call the ISelectQueryFilterService service before the SqlDataSource executes a SELECT query.

Implementation details

Authentication

For ease of demonstration, this example uses a simulated user login (without actual verification) that allows your code to use this user's identity.

User ID Retrieval

A custom UserService service processes the HttpContext and retrieves the user ID.

Security Filter

The SelectQueryFilterService service implements the ISelectQueryFilterService interface. The service calls the UserService service to get the ID of the user who is logged into the application.

The service's CustomizeFilterExpression method determines whether the query contains the specified tables, and adds conditional clauses that retrieve data rows where the StudentID column value matches the current User ID.

Note that the ISelectQueryFilterService does not allow you to modify the query passed to the CustomizeFilterExpression method. The method returns the CriteriaOperator that forms the WHERE clause for the original SELECT query.

Connection String

The QueryFilterServiceApp connection string is stored in the secret storage, as Microsoft recommends. Review the following article for more information: Protect secrets in development. The content of the secrets.json file is:

{
  "ConnectionStrings:QueryFilterServiceApp": "XpoProvider=MSSqlServer;Server=(local);Database=QueryFilterServiceApp;MultipleActiveResultSets=true;Integrated Security=True"
}

The RemoveXpoProviderKey method converts the DevExpress XPO connection string to a connection string that Entity Framework can use.