Hi Guys,
We're in the process of building a custom entity for mapping roles between users and organization units. We're experiencing issues when trying to insert multiple rows into the DB.
Our initial approach was to loop through each item and insert it, then save changes. The issue we encountered here was that only the last item processed by the loop would insert into the DB.
We've since tried moving the insertion and save into a separate method, which is called each time from the loop. Additionally, we reset the entity and CreatorUserIds to 0, so that they get re-autogenerated on insertion. The behavior that we are now experiencing is that when inserting three items, the first and last items insert into the DB, but the second items errors with the following message:
{"Cannot insert explicit value for identity column in table 'SfaTeamUserRoles' when IDENTITY_INSERT is set to OFF."} System.Exception {System.Data.SqlClient.SqlException}
As we understand it, IDENTITY_INSERT is to do with whether or not the table in question has an autogenerating PK - if it does, an ID should not be supplied into the insert. We're resetting the ID to 0 as the repository insert then seems to generate/receive an auto-id needed for the insertion. As mentioned, this works for the first and last items, but not the second. See the relevant classes below:
public async Task AssignOrUpdateRoles(CreateOrUpdateTeamUserRoleInput input)
{
var teamUserRole = ObjectMapper.Map<TeamUserRole>(input.TeamUser);
input.AssignedTeamUserRoles.Where(n => n.Assigned == "true").ToList().ForEach(async i =>
{
teamUserRole.Id = 0;
teamUserRole.CreatorUserId = null;
teamUserRole.TenantId = AbpSession.TenantId;
teamUserRole.RoleId = i.Id;
await AssignRoles(teamUserRole);
}
);
}
private async Task AssignRoles(TeamUserRole teamUserRole)
{
try
{
await _teamUserRoleRepository.InsertAsync(teamUserRole);
_unitOfWorkManager.Current.SaveChanges();
}
catch (DbUpdateException e)
{
SqlException s = e.InnerException.InnerException as SqlException;
}
}
[Table("SfaTeamUserRoles")]
public class TeamUserRole : AuditedEntity<long> , IMayHaveTenant
{
public int? TenantId { get; set; }
public virtual int RoleId { get; set; }
public virtual long OrganizationUnitId { get; set; }
public virtual long UserId { get; set; }
}
Is there a different repository method we should be using, or a different way of processing the data?
Thanks,
David
4 Answer(s)
-
0
Don't use Asynchronous inside List<T>.ForEach. Try foreachor for
-
0
@maliming thank you for your suggestion.
In the end, the issue wasn't whether or not it was synchronous/asynchronous but rather that the repository inserts needed to work with a new object each time, rather than one object that got modified for each insert.
public async Task AssignOrUpdateRoles(CreateOrUpdateTeamUserRoleInput input) { input.AssignedTeamUserRoles.Where(n => n.Assigned == "true").ToList().ForEach(async i => { var teamUserRole = new TeamUserRole { OrganizationUnitId = input.TeamUser.OrganizationUnitId, TenantId = AbpSession.TenantId, UserId = input.TeamUser.UserId, RoleId = i.Id }; if (!_teamUserRoleRepository.GetAll().Any(n => n.UserId == teamUserRole.UserId && n.RoleId == teamUserRole.RoleId && n.OrganizationUnitId == teamUserRole.OrganizationUnitId && (teamUserRole.TenantId == null || n.TenantId == teamUserRole.TenantId))) { await AssignRoles(teamUserRole); } } ); } -
0
I think the problem lies in the asynchronous of foreach. If you haven't solved the email, you can send the project to me (you can streamline the code)
<a href="mailto:[email protected]">[email protected]</a>
-
0
The main problem was as @DavidHarrison realised. After the first Insert, the entity is already tracked. However, it's still not good to perform async Inserts in List<T>.ForEach as @maliming has pointed out.
var assignedTeamUserRoles = input.AssignedTeamUserRoles.Where(n => n.Assigned == "true").ToList(); foreach (var i in assignedTeamUserRoles) { // await ... }
