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.
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.
IdentityLinkUserRepository.GetListAsync()
during authenticationSystem.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+<InitializeReaderAsync>d__22.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=9.0.7.0)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy+<ExecuteAsync>d__7`2.MoveNext (Microsoft.EntityFrameworkCore.SqlServer, Version=9.0.4.0)
at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable`1+AsyncEnumerator+<MoveNextAsync>d__21.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=9.0.7.0)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions+<ToListAsync>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)
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:
The query should use an efficient approach such as:
IN
clauses instead of individual parameter exclusionsThe IdentityLinkUserManager.GetListAsync()
method uses a recursive-like approach that:
includeIndirect: true
, iteratively finds additional linksThe issue is in the repository implementation at:
Volo.Abp.Identity.EntityFrameworkCore.EfCoreIdentityLinkUserRepository.GetListAsync()
Volo.Abp.Identity.IdentityLinkUserManager.GetListAsync()
This issue has been reported previously by multiple customers:
ABP Support Question #4568: "Linked Account modal is too slow"
GetListByIdsAsync
method)ABP Support Question #9859: "Linked Accounts - The loading time of the modal is too long"
includeIndirect: false
ABP Support Question #9631: "ABP EF Core horribly slow with 2k related entities"
AbpEfCoreNavigationHelper
performanceWe 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:
includeIndirect: false
to prevent parameter explosionGetListByIdsAsync()
for batch fetching (added in PR #15892)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:
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)
);
";
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);
}
| 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).
We request that ABP Framework address this performance issue in a future release by:
This issue affects production stability and will become a blocking issue as our customer base and linked user count grows.
This is in the abp identity management module.
Steps:
Users can set the new tenant password to the existing one
Steps:
Expected: Users should see the error - Entered an Existing password
cc: @<fd493ccd-86a6-6fea-b933-038d8ccbb6fc>
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?
Hi there.
Yes it's still an issue.
Here is the video
https://drive.google.com/file/d/1b7krwYqcBSHcI_ZZumGrWN6hIRk9Evb1/view?usp=drive_link