IdentityLinkUser Query Performance Issue - Parameter Explosion #9978

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](http://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