Activities of "dhill"

I will test it next week. Thanks so much for the help.

Hi,

Customizing the repository seems potentially risky in the situation that this code changes in future releases.
Do you have any established fixes or recommendations?
Does this repository experience a lot of changes that we need to closely track?
It seems hacky to override delivered code for what seems like a design issue / bug.

Please let us know your thoughts.

Environment Information

• ABP Framework Version: 9.2.1 (Commercial)
• ABP Modules: Identity Pro, Account Pro
• Database Provider: SQL Server (via Entity Framework Core)
• Application Type: Blazor Server + WASM Hybrid
• Database: SQL Server
• Number of Tenants: ~50
• Number of Identity Links: ~30 active links

Issue Summary

The IdentityLinkUserRepository.GetListAsync() method generates SQL queries with an excessive number of parameters (60+) when checking for user identity links, causing query timeouts and blocking user sign-in operations. The query complexity grows exponentially with the number of linked users, approaching SQL Server's 2100 parameter limit.

Steps to Reproduce

1. Create a multi-tenant application with Identity Links enabled
2. Link a user account across multiple tenants (30+ links)
3. Attempt to sign in with a linked user account
4. The sign-in process calls IdentityLinkUserRepository.GetListAsync() during authentication
5. Query times out after several seconds, causing TaskCanceledException

Actual Behavior

Stack Trace

System.Threading.Tasks.TaskCanceledException:
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Private.CoreLib, Version=9.0.0.0)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand+<ExecuteReaderAsync>d__18.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=9.0.7.0)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1+AsyncEnumerator+&lt;InitializeReaderAsync&gt;d__22.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=9.0.7.0)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+&lt;ExecuteAsync&gt;d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=9.0.4.0)
   at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1+AsyncEnumerator+&lt;MoveNextAsync&gt;d__21.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=9.0.7.0)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+&lt;ToListAsync&gt;d__67`1.MoveNext (Microsoft.EntityFrameworkCore, Version=9.0.7.0)
   at Volo.Abp.Identity.EntityFrameworkCore.EfCoreIdentityLinkUserRepository+<GetListAsync>d__2.MoveNext (Volo.Abp.Identity.EntityFrameworkCore, Version=9.2.1.0)
   at Volo.Abp.Identity.IdentityLinkUserManager+<GetListAsync>d__10.MoveNext (Volo.Abp.Identity.Domain, Version=9.2.1.0)
   at Volo.Abp.Identity.IdentityLinkUserManager+<IsLinkedAsync>d__12.MoveNext (Volo.Abp.Identity.Domain, Version=9.2.1.0)
   at Volo.Abp.Account.IdentityLinkUserAppService+<IsLinkedAsync>d__13.MoveNext (Volo.Abp.Account.Pro.Public.Application, Version=9.2.1.0)

Generated SQL Query (Partial)

The query contains repetitive exclusion patterns with 60+ parameters:

WHERE
  (([a].[SourceUserId] = @__linkUserInfo_UserId_0 AND [a].[SourceTenantId] = @__linkUserInfo_TenantId_1)
   OR ([a].[TargetUserId] = @__linkUserInfo_UserId_0 AND [a].[TargetTenantId] = @__linkUserInfo_TenantId_1))
  AND ([a].[SourceTenantId] <> @__userInfo_TenantId_2 OR [a].[SourceTenantId] IS NULL OR [a].[SourceUserId] <> @__userInfo_UserId_3)
  AND ([a].[TargetTenantId] <> @__userInfo_TenantId_2 OR [a].[TargetTenantId] IS NULL OR [a].[TargetUserId] <> @__userInfo_UserId_3)
  AND ([a].[SourceTenantId] <> @__userInfo_TenantId_4 OR [a].[SourceTenantId] IS NULL OR [a].[SourceUserId] <> @__userInfo_UserId_5)
  AND ([a].[TargetTenantId] <> @__userInfo_TenantId_4 OR [a].[TargetTenantId] IS NULL OR [a].[TargetUserId] <> @__userInfo_UserId_5)
  ... [repeating ~30 times with incrementing parameter numbers up to @__userInfo_UserId_61]

Pattern Analysis:

• Each excluded user requires 2 parameters (TenantId + UserId)
• With 30 linked users, the query uses 60+ parameters
• SQL Server has a hard limit of 2100 parameters per query
• At current growth rate, system will fail completely with 1050+ linked users

Expected Behavior

The query should use an efficient approach such as:

1. Temporary tables or table-valued parameters
2. SQL Server Recursive CTEs
3. Batch fetching with IN clauses instead of individual parameter exclusions
4. Proper query optimization that doesn't scale O(n²) with linked user count

Root Cause Analysis

The IdentityLinkUserManager.GetListAsync() method uses a recursive-like approach that:

1. Fetches initial direct links
2. For includeIndirect: true, iteratively finds additional links
3. Excludes already-found users by adding them individually to the WHERE clause
4. Each exclusion adds 2 parameters (SourceTenantId, SourceUserId, TargetTenantId, TargetUserId)
5. No apparent limit on recursive depth or parameter count

Code Location

The issue is in the repository implementation at:

• Volo.Abp.Identity.EntityFrameworkCore.EfCoreIdentityLinkUserRepository.GetListAsync()
• Called by Volo.Abp.Identity.IdentityLinkUserManager.GetListAsync()

Related Issues

This issue has been reported previously by multiple customers:

1. ABP Support Question #4568: "Linked Account modal is too slow"

   • Reported in ABP 5.3.3
   • Same root cause: excessive round-trips and parameter proliferation
   • Partial fix in PR #15892 (added GetListByIdsAsync method)
   • Not fully resolved

2. ABP Support Question #9859: "Linked Accounts - The loading time of the modal is too long"

   • Referenced in our custom implementation code comments
   • Same performance issue with linked accounts modal
   • Required customer to implement workaround using includeIndirect: false

3. ABP Support Question #9631: "ABP EF Core horribly slow with 2k related entities"

   • Fixed in ABP 9.2.3 via PR #23329
   • Related to AbpEfCoreNavigationHelper performance
   • Different but compounding issue that affected overall query performance

Current Workaround

We have implemented a temporary workaround in our CustomSignInManager:

// Wrap identity link check in try-catch to prevent sign-in blocking
try
{
    var links = await IdentityLinkUserRepository.GetListAsync(
        new IdentityLinkUserInfo(user.Id, user.TenantId),
        cancellationToken);

    if (links != null && links.Count > 0)
    {
        // Set server-side rendering mode
        Context.Response.Cookies.Append(...);
    }
}
catch (OperationCanceledException ex)
{
    Logger.LogWarning(ex,
        "Identity link check cancelled during sign-in for user {UserId}. Defaulting to client-side rendering.",
        user.Id);
    // Allow sign-in to proceed with default rendering mode
}
catch (Exception ex)
{
    Logger.LogError(ex,
        "Failed to check identity links for user {UserId}. Defaulting to client-side rendering.",
        user.Id);
    // Allow sign-in to proceed with default rendering mode
}

Additionally, we have implemented a custom IdentityLinkUserAppService that works around this issue:

File: src/SafetyPlusWeb.Blazor/Services/CustomIdentityLinkUserAppService.cs

[Dependency(ReplaceServices = true)]
[ExposeServices(typeof(IIdentityLinkUserAppService))]
public class CustomIdentityLinkUserAppService : IdentityLinkUserAppService, ITransientDependency
{
    public override async Task<ListResultDto<LinkUserDto>> GetAllListAsync()
    {
        var currentUserId = CurrentUser.GetId();
        var currentTenantId = CurrentTenant.Id;
        using (CurrentTenant.Change(null))
        {
            // WORKAROUND: Get all identity link users WITHOUT including indirect links
            // This avoids the parameter explosion issue described above
            // See https://abp.io/support/questions/9859/Linked-Accounts---The-loading-time-of-the-modal-is-too-long
            var linkUsers = await IdentityLinkUserManager.GetListAsync(
                new IdentityLinkUserInfo(currentUserId, currentTenantId),
                includeIndirect: false); // CRITICAL: false prevents recursive parameter explosion

            // Manual processing of direct links to build the complete user list
            var allLinkUsers = linkUsers.Select(x => new LinkUserDto
            {
                TargetTenantId = x.TargetTenantId,
                TargetUserId = x.TargetUserId,
                DirectlyLinked = x.SourceTenantId == currentTenantId && x.SourceUserId == currentUserId
                              || x.TargetTenantId == currentTenantId && x.TargetUserId == currentUserId
            }).Concat(linkUsers.Select(x => new LinkUserDto
            {
                TargetTenantId = x.SourceTenantId,
                TargetUserId = x.SourceUserId,
                DirectlyLinked = x.SourceTenantId == currentTenantId && x.SourceUserId == currentUserId
                              || x.TargetTenantId == currentTenantId && x.TargetUserId == currentUserId
            })).GroupBy(x => new { x.TargetTenantId, x.TargetUserId })
                .Select(x => x.OrderByDescending(y => y.DirectlyLinked).First())
                .Where(x => x.TargetTenantId != currentTenantId || x.TargetUserId != currentUserId)
                .ToList();

            if (!allLinkUsers.Any())
            {
                return new ListResultDto<LinkUserDto>(new List<LinkUserDto>());
            }

            // Batch fetch users by tenant to reduce round-trips
            var userDto = new List<LinkUserDto>(allLinkUsers.Count);
            foreach (var userGroup in allLinkUsers.GroupBy(x => x.TargetTenantId))
            {
                var tenantId = userGroup.Key;
                TenantConfiguration tenant = null;
                if (tenantId.HasValue)
                {
                    tenant = await TenantStore.FindAsync(tenantId.Value);
                }

                using (CurrentTenant.Change(tenantId))
                {
                    // Use GetListByIdsAsync for batch fetching (from PR [#15892](https://abp.io/QA/Questions/15892))
                    var users = await IdentityUserRepository.GetListByIdsAsync(
                        userGroup.Select(x => x.TargetUserId));

                    foreach (var user in users)
                    {
                        userDto.Add(new LinkUserDto
                        {
                            TargetUserId = user.Id,
                            TargetUserName = user.UserName,
                            TargetTenantId = tenant?.Id,
                            TargetTenantName = tenant?.Name,
                            DirectlyLinked = userGroup.FirstOrDefault(x => x.TargetUserId == user.Id)?.DirectlyLinked ?? false
                        });
                    }
                }
            }

            return new ListResultDto<LinkUserDto>(userDto);
        }
    }
}

Key Points:

1. Uses includeIndirect: false to prevent parameter explosion
2. Manually processes direct links to build the complete picture
3. Uses GetListByIdsAsync() for batch fetching (added in PR #15892)
4. This approach works but requires application-level logic that should be in the repository

Proposed Solutions

Option 1: Recursive CTE Implementation (Recommended)

Replace the iterative approach with SQL Server Recursive CTE:

public override async Task<List<IdentityLinkUser>> GetListAsync(
    IdentityLinkUserInfo linkUserInfo,
    bool includeIndirect = false,
    CancellationToken cancellationToken = default)
{
    if (!includeIndirect)
    {
        return await base.GetListAsync(linkUserInfo, false, cancellationToken);
    }

    using var dbContext = await GetDbContextAsync();

    var query = @"
        WITH LinkChain AS (
            -- Base case: direct links
            SELECT Id, SourceUserId, SourceTenantId, TargetUserId, TargetTenantId
            FROM AbpLinkUsers
            WHERE (SourceUserId = @userId AND SourceTenantId = @tenantId)
               OR (TargetUserId = @userId AND TargetTenantId = @tenantId)

            UNION ALL

            -- Recursive case: follow the chain
            SELECT l.Id, l.SourceUserId, l.SourceTenantId, l.TargetUserId, l.TargetTenantId
            FROM AbpLinkUsers l
            INNER JOIN LinkChain c ON
                (l.SourceUserId = c.TargetUserId AND l.SourceTenantId = c.TargetTenantId)
                OR (l.TargetUserId = c.SourceUserId AND l.TargetTenantId = c.SourceTenantId)
        )
        SELECT DISTINCT * FROM LinkChain;
    ";

    return await dbContext.Set<IdentityLinkUser>()
        .FromSqlRaw(query,
            new SqlParameter("@userId", linkUserInfo.UserId),
            new SqlParameter("@tenantId", (object)linkUserInfo.TenantId ?? DBNull.Value))
        .ToListAsync(cancellationToken);
}

Benefits:

• Only 2 parameters regardless of link count
• Single database round-trip
• Leverages SQL Server's optimized CTE implementation
• O(n) complexity instead of O(n²)

Option 2: Temporary Table Approach

Use a temporary table to store found users and join against it:

// Create temp table with initial user
await dbContext.Database.ExecuteSqlRawAsync(@"
    CREATE TABLE #FoundUsers (UserId uniqueidentifier, TenantId uniqueidentifier NULL);
    INSERT INTO #FoundUsers VALUES (@userId, @tenantId);
", parameters);

// Query using temp table join
var query = @"
    SELECT l.* FROM AbpLinkUsers l
    WHERE EXISTS (
        SELECT 1 FROM #FoundUsers f
        WHERE (l.SourceUserId = f.UserId AND l.SourceTenantId = f.TenantId)
           OR (l.TargetUserId = f.UserId AND l.TargetTenantId = f.TenantId)
    )
    AND NOT EXISTS (
        SELECT 1 FROM #FoundUsers f2
        WHERE (l.SourceUserId = f2.UserId AND l.SourceTenantId = f2.TenantId)
    );
";

Option 3: Batch Processing with IN Clauses

Fetch in batches using Contains() with chunking:

// Process in batches of 500 to stay well under 2100 parameter limit
const int batchSize = 500;
var allLinks = new List<IdentityLinkUser>();

for (int i = 0; i < userIds.Count; i += batchSize)
{
    var batch = userIds.Skip(i).Take(batchSize).ToList();
    var batchLinks = await _repository.GetQueryableAsync()
        .Where(x => batch.Contains(x.SourceUserId) || batch.Contains(x.TargetUserId))
        .ToListAsync(cancellationToken);
    allLinks.AddRange(batchLinks);
}

Impact Assessment

Current Impact

• User sign-in blocked when identity link check times out
• Degraded user experience (30+ second delays)
• Production stability risk as link count grows

Risk Projection

| Linked Users | Parameters | Status | Risk Level | |--------------|------------|--------|------------| | 0-10 | 0-20 | Working | Low | | 10-30 | 20-60 | Slow (current) | Medium | | 30-1000 | 60-2000 | Very Slow | High | | 1050+ | 2100+ | Query Fails | Critical |

With current growth, the system will completely fail when reaching 1050+ linked users (3% current capacity).

Request

We request that ABP Framework address this performance issue in a future release by:

1. Implementing a more efficient query strategy (Recursive CTE or temp tables)
2. Adding proper database indexes to the Identity module migrations
3. Providing query optimization for large-scale identity link networks
4. Adding configurable parameter limits or automatic batching

This issue affects production stability and will become a blocking issue as our customer base and linked user count grows.

Additional Context

• We are running ABP Framework 9.2.1 Commercial
• We have already upgraded to address the EF Core performance fix in 9.2.3
• The workaround allows sign-in to proceed but doesn't solve the underlying query inefficiency
• Similar issues have been reported since ABP 5.3.3 without a complete resolution

This is in the abp identity management module.

Steps:

1. Log in to the tenant
2. Add a new user
3. Change the added user's username
4. Open another browser
5. Log in using the provided user's credentials
6. Users are not seeing the user's 1st login - Password Change form

Users can set the new tenant password to the existing one

Steps:

1. Create a new tenant with the password Admin@123
2. After the creation, click on the Actions button of the tenant
3. Click on the Set Password option
4. Enter the same password "Admin@123"
5. Click on the Save button

Expected: Users should see the error - Entered an Existing password

cc: @<fd493ccd-86a6-6fea-b933-038d8ccbb6fc>

When users click on the browser back button from the forgot password page, they see the resubmission page Image

However, when they click on the Login button, they get navigated back to the main login page

The load time is more than 3 minutes for 100 tenants.

Can you please point me in the direction of which class we could look at / override?

The css solution makes sense. However, how do we improve performance?

ABP 9.2.2 with Blazor Web App

• Exception message and full stack trace:
• Steps to reproduce the issue:

The loading time of the modal is too long The Name field is getting out of bound on the Modal Image