/// <summary> /// Saves the entities (Insert or Update) into the database after authorization and validation. /// </summary> /// <returns>Optionally returns the same entities in their persisted READ form.</returns> protected virtual async Task <EntitiesResponse <TEntity> > SaveImplAsync(List <TEntityForSave> entities, SaveArguments args) { try { // Parse arguments var expand = ExpandExpression.Parse(args?.Expand); var returnEntities = args?.ReturnEntities ?? false; // Trim all strings as a preprocessing step entities.ForEach(e => TrimStringProperties(e)); // This implements field level security entities = await ApplyUpdatePermissionsMask(entities); // Start a transaction scope for save since it causes data modifications using var trx = ControllerUtilities.CreateTransaction(null, GetSaveTransactionOptions()); // Optional preprocessing await SavePreprocessAsync(entities); // Validate // Basic validation that applies to all entities ControllerUtilities.ValidateUniqueIds(entities, ModelState, _localizer); // Actual Validation await SaveValidateAsync(entities); if (!ModelState.IsValid) { throw new UnprocessableEntityException(ModelState); } // Save and retrieve Ids var ids = await SaveExecuteAsync(entities, expand, returnEntities); // Use the Ids to retrieve the items EntitiesResponse <TEntity> result = null; if (returnEntities && ids != null) { result = await GetByIdListAsync(ids.ToArray(), expand); } await PostProcess(result); // Commit and return await OnSaveCompleted(); trx.Complete(); return(result); } catch (Exception ex) { await OnSaveError(ex); throw ex; } }
public virtual async Task <ActionResult <EntitiesResponse <TDto> > > Save([FromBody] List <TDtoForSave> entities, [FromQuery] SaveArguments args) { // Note here we use lists https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netcore-2.1 // since the order is symantically relevant for reporting validation errors on the entities return(await CallAndHandleErrorsAsync(async() => { var result = await SaveImplAsync(entities, args); return Ok(result); })); }
protected override async Task <(List <RoleForQuery>, IQueryable <RoleForQuery>)> PersistAsync(List <RoleForSave> entities, SaveArguments args) { // Add created entities var roleIndices = entities.ToIndexDictionary(); DataTable rolesTable = ControllerUtilities.DataTable(entities, addIndex: true); var rolesTvp = new SqlParameter("Roles", rolesTable) { TypeName = $"dbo.{nameof(RoleForSave)}List", SqlDbType = SqlDbType.Structured }; // Filter out permissions that haven't changed for performance var permissionHeaderIndices = roleIndices.Keys.Select(role => (role.Permissions.Where(e => e.EntityState != null).ToList(), roleIndices[role])); DataTable permissionsTable = ControllerUtilities.DataTableWithHeaderIndex(permissionHeaderIndices, e => e.EntityState != null); var permissionsTvp = new SqlParameter("Permissions", permissionsTable) { TypeName = $"dbo.{nameof(PermissionForSave)}List", SqlDbType = SqlDbType.Structured }; var signatureHeaderIndices = roleIndices.Keys.Select(role => (role.Signatures.Where(e => e.EntityState != null).ToList(), roleIndices[role])); DataTable signaturesTable = ControllerUtilities.DataTableWithHeaderIndex(signatureHeaderIndices, e => e.EntityState != null); var signaturesTvp = new SqlParameter("Signatures", signaturesTable) { TypeName = $"dbo.{nameof(RequiredSignatureForSave)}List", SqlDbType = SqlDbType.Structured }; var memberHeaderIndices = roleIndices.Keys.Select(role => (role.Members, roleIndices[role])); DataTable membersTable = ControllerUtilities.DataTableWithHeaderIndex(memberHeaderIndices, e => e.EntityState != null); var membersTvp = new SqlParameter("Members", membersTable) { TypeName = $"dbo.{nameof(RoleMembershipForSave)}List", SqlDbType = SqlDbType.Structured }; string saveSql = $@" -- TODO: PermissionsVersion DECLARE @NewId UNIQUEIDENTIFIER = NEWID(); UPDATE [dbo].[LocalUsers] SET PermissionsVersion = @NewId; -- Procedure: Roles__Save DECLARE @IndexedIds [dbo].[IndexedIdList], @PermissionsIndexedIds [dbo].[IndexedIdList]; DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId')); DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET(); DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId')); DELETE FROM [dbo].[Permissions] WHERE [Id] IN (SELECT [Id] FROM @Permissions WHERE [EntityState] = N'Deleted'); DELETE FROM [dbo].[Permissions] WHERE [Id] IN (SELECT [Id] FROM @Signatures WHERE [EntityState] = N'Deleted'); DELETE FROM [dbo].[RoleMemberships] WHERE [Id] IN (SELECT [Id] FROM @Members WHERE [EntityState] = N'Deleted'); INSERT INTO @IndexedIds([Index], [Id]) SELECT x.[Index], x.[Id] FROM ( MERGE INTO [dbo].[Roles] AS t USING ( SELECT [Index], [Id], [Name], [Name2], [IsPublic], [Code] FROM @Roles WHERE [EntityState] IN (N'Inserted', N'Updated') ) AS s ON (t.Id = s.Id) WHEN MATCHED THEN UPDATE SET t.[Name] = s.[Name], t.[Name2] = s.[Name2], t.[IsPublic] = s.[IsPublic], t.[Code] = s.[Code], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId WHEN NOT MATCHED THEN INSERT ( [TenantId], [Name], [Name2], [IsPublic], [Code], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById] ) VALUES ( @TenantId, s.[Name], s.[Name2], s.[IsPublic], s.[Code], @Now, @UserId, @Now, @UserId ) OUTPUT s.[Index], inserted.[Id] ) As x; MERGE INTO [dbo].[Permissions] AS t USING ( SELECT L.[Index], L.[Id], II.[Id] AS [RoleId], [ViewId], [Level], L.[Criteria], L.[Mask], L.[Memo] FROM @Permissions L JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index] WHERE L.[EntityState] IN (N'Inserted', N'Updated') UNION SELECT L.[Index], L.[Id], II.[Id] AS [RoleId], [ViewId], 'Sign' AS [Level], L.[Criteria], NULL as [Mask], L.[Memo] FROM @Signatures L JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index] WHERE L.[EntityState] IN (N'Inserted', N'Updated') ) AS s ON t.Id = s.Id WHEN MATCHED THEN UPDATE SET t.[ViewId] = s.[ViewId], t.[Level] = s.[Level], t.[Criteria] = s.[Criteria], t.[Mask] = s.[Mask], t.[Memo] = s.[Memo], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId WHEN NOT MATCHED THEN INSERT ([TenantId], [RoleId], [ViewId], [Level], [Criteria], [Mask], [Memo], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]) VALUES (@TenantId, s.[RoleId], s.[ViewId], s.[Level], s.[Criteria], s.[Mask], s.[Memo], @Now, @UserId, @Now, @UserId); MERGE INTO [dbo].[RoleMemberships] AS t USING ( SELECT L.[Index], L.[Id], II.[Id] AS [RoleId], [UserId], [Memo] FROM @Members L JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index] WHERE L.[EntityState] IN (N'Inserted', N'Updated') ) AS s ON t.Id = s.Id WHEN MATCHED THEN UPDATE SET t.[UserId] = s.[UserId], t.[RoleId] = s.[RoleId], t.[Memo] = s.[Memo], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId WHEN NOT MATCHED THEN INSERT ([TenantId], [UserId], [RoleId], [Memo], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]) VALUES (@TenantId, s.[UserId], s.[RoleId], s.[Memo], @Now, @UserId, @Now, @UserId); "; // Optimization if (!(args.ReturnEntities ?? false)) { // IF no returned items are expected, simply execute a non-Query and return an empty list; await _db.Database.ExecuteSqlCommandAsync(saveSql, rolesTvp, permissionsTvp, signaturesTvp, membersTvp); return(new List <RoleForQuery>(), null); } else { // If returned items are expected, append a select statement to the SQL command saveSql = saveSql += "SELECT * FROM @IndexedIds;"; // Retrieve the map from Indexes to Ids var indexedIds = await _db.Saving.FromSql(saveSql, rolesTvp, permissionsTvp, signaturesTvp, membersTvp).ToListAsync(); var idsString = string.Join(",", indexedIds.Select(e => e.Id)); var q = _db.VW_Roles.FromSql($"SELECT * FROM [dbo].[VW_Roles] WHERE Id IN (SELECT CONVERT(INT, VALUE) AS Id FROM STRING_SPLIT({idsString}, ','))"); q = Expand(q, args.Expand); // Includes var savedEntities = await q.ToListAsync(); // SQL Server does not guarantee order, so make sure the result is sorted according to the initial index Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index); var sortedSavedEntities = new RoleForQuery[savedEntities.Count]; foreach (var item in savedEntities) { int index = indices[item.Id.Value]; sortedSavedEntities[index] = item; } // Return the sorted collection return(sortedSavedEntities.ToList(), q); } }
public virtual async Task <ActionResult <EntitiesResponse <TEntity> > > Save([FromBody] List <TEntityForSave> entities, [FromQuery] SaveArguments args) { // Note here we use lists https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netcore-2.1 // since the order is semantically relevant for reporting validation errors // Basic sanity check, to prevent null entities if (entities == null && !ModelState.IsValid) { if (ModelState.IsValid) { return(BadRequest("Request Body is empty.")); } else { return(UnprocessableEntity(ModelState)); } } // Calculate server time at the very beginning for consistency var serverTime = DateTimeOffset.UtcNow; // Load the data var service = GetCrudService(); var result = await service.Save(entities, args); await OnSuccessfulSave(result); // Transform it and return the result if (args?.ReturnEntities ?? false) { // Transform the entities as an EntitiesResponse var response = TransformToEntitiesResponse(result, serverTime, cancellation: default); // Return the response return(Ok(response)); } else { // Return 200 return(Ok()); } }
/// <summary> /// Persists the entities in the database, either creating them or updating them depending on the EntityState /// </summary> protected abstract Task <List <TModel> > PersistAsync(List <TDtoForSave> entities, SaveArguments args);
protected override async Task <List <M.LocalUser> > PersistAsync(List <LocalUserForSave> entities, SaveArguments args) { // Make all the emails small case entities.ForEach(e => e.Email = e.Email.ToLower()); // Get the inserted users var insertedEntities = entities.Where(e => e.EntityState == EntityStates.Inserted); // Query the manager DB for matching emails, here I use the CodeList user-defined table type // of the manager DB, since I only want to pass a list of strings, no need to defined a new type var insertedEmails = insertedEntities.Select(e => new { Code = e.Email }); var emailsTable = DataTable(insertedEmails); var emailsTvp = new SqlParameter("Emails", emailsTable) { TypeName = $"dbo.MCodeList", SqlDbType = SqlDbType.Structured }; var tenantId = new SqlParameter("TenantId", _tenantIdProvider.GetTenantId()); var globalMatches = await _adminDb.GlobalUsersMatches.FromSql($@" DECLARE @IndexedIds [dbo].[IdList]; -- Insert new users INSERT INTO @IndexedIds([Id]) SELECT x.[Id] FROM ( MERGE INTO [dbo].[GlobalUsers] AS t USING ( SELECT [Code] as [Email] FROM @Emails ) AS s ON (t.Email = s.Email) WHEN NOT MATCHED THEN INSERT ([Email]) VALUES (s.[Email]) OUTPUT inserted.[Id] ) As x; -- Insert memberships INSERT INTO [dbo].[TenantMemberships] (UserId, TenantId) SELECT Id, @TenantId FROM @IndexedIds; -- Return existing users SELECT E.[Code] AS [Email], GU.[ExternalId] AS [ExternalId] FROM [dbo].[GlobalUsers] GU JOIN @Emails E ON GU.Email = E.Code WHERE GU.ExternalId IS NOT NULL", emailsTvp, tenantId).ToDictionaryAsync(e => e.Email); // Add created entities var localUsersIndices = entities.ToIndexDictionary(); var localUsersTable = LocalUsersDataTable(entities, globalMatches); var localUsersTvp = new SqlParameter("LocalUsers", localUsersTable) { TypeName = $"dbo.{nameof(LocalUserForSave)}List", SqlDbType = SqlDbType.Structured }; // Filter out roles that haven't changed for performance var rolesHeaderIndices = localUsersIndices.Keys.Select(localUser => (localUser.Roles, HeaderIndex: localUsersIndices[localUser])); DataTable rolesTable = DataTableWithHeaderIndex(rolesHeaderIndices, e => e.EntityState != null); var rolesTvp = new SqlParameter("RoleMemberships", rolesTable) { TypeName = $"dbo.{nameof(RoleMembershipForSave)}List", SqlDbType = SqlDbType.Structured }; string saveSql = $@" -- Procedure: LocalUsers__Save DECLARE @IndexedIds [dbo].[IndexedIdList]; DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId')); DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET(); DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId')); DELETE FROM [dbo].[RoleMemberships] WHERE [Id] IN (SELECT [Id] FROM @RoleMemberships WHERE [EntityState] = N'Deleted'); INSERT INTO @IndexedIds([Index], [Id]) SELECT x.[Index], x.[Id] FROM ( MERGE INTO [dbo].[LocalUsers] AS t USING ( SELECT [Index], [Id], [Name], [Name2], [Email], [ExternalId], [AgentId] FROM @LocalUsers WHERE [EntityState] IN (N'Inserted', N'Updated') ) AS s ON (t.Id = s.Id) WHEN MATCHED THEN UPDATE SET t.[Name] = s.[Name], t.[Name2] = s.[Name2], -- t.[Email] = s.[Email], -- t.[ExternalId] = s.[ExternalId], t.[AgentId] = s.[AgentId], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId, t.[PermissionsVersion] = NEWID(), -- in case the permissions have changed t.[UserSettingsVersion] = NEWID() -- in case the permissions have changed WHEN NOT MATCHED THEN INSERT ( [TenantId], [Name], [Name2], [Email], [ExternalId], [AgentId], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById] ) VALUES ( @TenantId, s.[Name], s.[Name2], s.[Email], s.[ExternalId], s.[AgentId], @Now, @UserId, @Now, @UserId ) OUTPUT s.[Index], inserted.[Id] ) As x; MERGE INTO [dbo].[RoleMemberships] AS t USING ( SELECT L.[Index], L.[Id], II.[Id] AS [UserId], [RoleId], [Memo] FROM @RoleMemberships L JOIN @IndexedIds II ON L.[HeaderIndex] = II.[Index] WHERE L.[EntityState] IN (N'Inserted', N'Updated') ) AS s ON t.Id = s.Id WHEN MATCHED THEN UPDATE SET t.[UserId] = s.[UserId], t.[RoleId] = s.[RoleId], t.[Memo] = s.[Memo], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId WHEN NOT MATCHED THEN INSERT ([TenantId], [UserId], [RoleId], [Memo], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]) VALUES (@TenantId, s.[UserId], s.[RoleId], s.[Memo], @Now, @UserId, @Now, @UserId); "; // Prepare the list of users whose profile picture has changed: var usersWithModifiedImgs = entities.Where(e => e.Image != null); bool newPictures = usersWithModifiedImgs.Any(); bool returnEntities = (args.ReturnEntities ?? false); // Optimization if (!returnEntities && !newPictures) { // IF no returned items are expected, simply execute a non-Query and return an empty list; await _db.Database.ExecuteSqlCommandAsync(saveSql, localUsersTvp, rolesTvp); return(new List <M.LocalUser>()); } else { // If returned items are expected, append a select statement to the SQL command saveSql = saveSql += "SELECT * FROM @IndexedIds;"; // Retrieve the map from Indexes to Ids var indexedIds = await _db.Saving.FromSql(saveSql, localUsersTvp, rolesTvp).ToListAsync(); var idsString = string.Join(",", indexedIds.Select(e => e.Id)); var q = _db.LocalUsers.FromSql($"SELECT * FROM [dbo].[LocalUsers] WHERE Id IN (SELECT CONVERT(INT, VALUE) AS Id FROM STRING_SPLIT({idsString}, ','))"); q = Expand(q, args.Expand); // includes var savedEntities = await q.AsNoTracking().ToListAsync(); // SQL Server does not guarantee order, so make sure the result is sorted according to the initial index Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index); var sortedSavedEntities = new M.LocalUser[savedEntities.Count]; foreach (var item in savedEntities) { int index = indices[item.Id]; sortedSavedEntities[index] = item; } // The code inside here is not optimized for bulk, we assume for now // that users will be entering images one at a time if (newPictures) { var entitiesDic = entities.ToIndexDictionary(); // Retrieve blobs to delete var blobsToDelete = usersWithModifiedImgs.Where(e => e.EntityState == EntityStates.Updated) .Select(u => sortedSavedEntities[entitiesDic[u]].ImageId).Where(e => e != null).Select(e => BlobName(e)).ToList(); var blobsToSave = new List <(string name, byte[] content)>(); foreach (var user in usersWithModifiedImgs) { // Get the Id of the user int index = entitiesDic[user]; var savedEntity = sortedSavedEntities[entitiesDic[user]]; int id = savedEntity.Id; if (user.Image.Length == 0) { // We simply NULL image Id await _db.Database.ExecuteSqlCommandAsync($@"UPDATE [dbo].[LocalUsers] SET ImageId = NULL WHERE [Id] = {id}"); savedEntity.ImageId = null; } else { // We create a new Image Id string imageId = Guid.NewGuid().ToString(); await _db.Database.ExecuteSqlCommandAsync($@"UPDATE [dbo].[LocalUsers] SET ImageId = {imageId} WHERE [Id] = {id}"); savedEntity.ImageId = imageId; // We make the image smaller and turn it into JPEG var imageBytes = user.Image; using (var image = Image.Load(imageBytes)) { // Resize to 128x128px image.Mutate(c => c.Resize(new ResizeOptions { // 'Max' mode maintains the aspect ratio and keeps the entire image Mode = ResizeMode.Max, Size = new Size(128), Position = AnchorPositionMode.Center })); // some image formats that support transparent regions // these regions will turn black in JPEG format unless we do this image.Mutate(c => c.BackgroundColor(Rgba32.White));; // Save as JPEG var memoryStream = new MemoryStream(); image.SaveAsJpeg(memoryStream); imageBytes = memoryStream.ToArray(); // Note: JPEG is the format of choice for photography // for such pictures it provides better quality at a lower size // Since these pictures are expected to be mostly photographs // we save them as JPEGs } // Add it to blobs to save blobsToSave.Add((BlobName(imageId), imageBytes)); } } // Delete the blobs retrieved earlier if (blobsToDelete.Any()) { await _blobService.DeleteBlobs(blobsToDelete); } // Save new blobs if any if (blobsToSave.Any()) { await _blobService.SaveBlobs(blobsToSave); } // Note: Since the blob service is not a transactional resource it is good to do the blob calls // near the end to minimize the chance of modifying blobs first only to have the transaction roll back later } // Return the saved entities if requested if (!returnEntities) { return(new List <M.LocalUser>()); } else { // Return the sorted collection return(sortedSavedEntities.ToList()); } } }
public Task <ActionResult <SaveGlobalSettingsResponse> > Save([FromBody] GlobalSettingsForSave settingsForSave, [FromQuery] SaveArguments args) { // Authorized access (Criteria are not supported here) // TODO Authorize //var updatePermissions = await ControllerUtilities.GetPermissions(_db.AbstractPermissions, PermissionLevel.Update, "settings"); //if (!updatePermissions.Any()) //{ // return StatusCode(403); //} //try //{ // // Trim all string fields just in case // settingsForSave.TrimStringProperties(); // // Validate // ValidateAndPreprocessSettings(settingsForSave); // if (!ModelState.IsValid) // { // return UnprocessableEntity(ModelState); // } // // Persist // M.GlobalSettings mSettings = await _repo.GlobalSettings.FirstOrDefaultAsync(); // if (mSettings == null) // { // // This should never happen // return BadRequest("Global settings have not been initialized"); // } // _mapper.Map(settingsForSave, mSettings); // mSettings.SettingsVersion = Guid.NewGuid(); // prompts clients to refresh // await _repo.SaveChangesAsync(); // // IF requested, return the updated entity // if (args.ReturnEntities ?? false) // { // // IF requested, return the same response you would get from a GET // var res = await GetImpl(new GetByIdArguments { Expand = args.Expand }); // var result = new SaveGlobalSettingsResponse // { // CollectionName = res.CollectionName, // Result = res.Result, // RelatedEntities = res.RelatedEntities, // SettingsForClient = GetForClientImpl() // }; // return result; // } // else // { // return Ok(); // } //} //catch (Exception ex) //{ // _logger.LogError($"Error: {ex.Message} {ex.StackTrace}"); // return BadRequest(ex.Message); //} throw new NotImplementedException(); }
public async Task <(TSettings, Versioned <SettingsForClient>)> SaveSettings(TSettingsForSave settingsForSave, SaveArguments args) { var updatePermissions = await UserPermissions(Constants.Update, cancellation : default); if (!updatePermissions.Any()) { throw new ForbiddenException(); } // Trim all string fields just in case settingsForSave.TrimStringProperties(); // Preprocess await Preprocess(settingsForSave); // Validate await SaveValidate(settingsForSave); ModelState.ThrowIfInvalid(); // Persist await SaveExecute(settingsForSave, args); // Update the settings cache var tenantId = _tenantIdAccessor.GetTenantId(); var settingsForClient = await LoadSettingsForClient(GetRepository(), cancellation : default); _settingsCache.SetSettings(tenantId, settingsForClient); // If requested, return the updated entity if (args.ReturnEntities ?? false) { // If requested, return the same response you would get from a GET var res = await GetSettings(args, cancellation : default); return(res, settingsForClient); } else { return(default);
public async Task <ActionResult <SaveSettingsResponse <TSettings> > > Save([FromBody] TSettingsForSave settingsForSave, [FromQuery] SaveArguments args) { return(await ControllerUtilities.InvokeActionImpl(async() => { var _service = GetSettingsService(); var(settings, settingsForClient) = await _service.SaveSettings(settingsForSave, args); var singleton = new TSettings[] { settings }; var relatedEntities = ControllerUtilities.FlattenAndTrim(singleton, cancellation: default); var result = new SaveSettingsResponse <TSettings> { Result = settings, RelatedEntities = relatedEntities, SettingsForClient = settingsForClient }; return Ok(result); }, _logger)); }
/// <summary> /// Saves the entities (Insert or Update) into the database after authorization and validation /// </summary> /// <returns>Optionally returns the same entities in their persisted READ form</returns> protected virtual async Task <EntitiesResponse <TDto> > SaveImplAsync(List <TDtoForSave> entities, SaveArguments args) { await CheckUpdatePermissions(entities, args); // Trim all strings as a preprocessing step entities.ForEach(e => TrimStringProperties(e)); using (var trx = await BeginSaveTransaction()) { try { // Validate await ValidateAsync(entities); if (!ModelState.IsValid) { throw new UnprocessableEntityException(ModelState); } // Save var(memoryList, query) = await PersistAsync(entities, args); // Add the metadata ApplySelectAndAddMetadata(memoryList, args.Expand, null); // Apply the permission masks (setting restricted fields to null) and adjust the metadata accordingly if (memoryList != null && memoryList.Any()) { var permissions = await UserPermissions(PermissionLevel.Read); var defaultMask = GetDefaultMask(); await ApplyReadPermissionsMask(memoryList, query, permissions, defaultMask); } // Flatten related entities and map each to its respective DTO var relatedEntities = FlattenRelatedEntitiesAndTrim(memoryList, args.Expand); // Map the primary result to DTOs as well var resultData = Mapper.Map <List <TDto> >(memoryList); // Prepare the result in a response object var result = new EntitiesResponse <TDto> { Data = resultData, RelatedEntities = relatedEntities, CollectionName = GetCollectionName(typeof(TDto)) }; // Commit and return trx.Commit(); return(result); } catch (Exception ex) { // Roll back the transaction trx.Rollback(); throw ex; } } }
/// <summary> /// Persists the entities in the database, either creating them or updating them depending on the EntityState /// </summary> protected abstract Task <(List <TDtoForQuery>, IQueryable <TDtoForQuery>)> PersistAsync(List <TDtoForSave> entities, SaveArguments args);
protected override async Task <List <M.Agent> > PersistAsync(List <AgentForSave> entities, SaveArguments args) { // Some properties are always set to null for organizations string agentType = ViewId(); if (agentType == ORGANIZATION) { entities.ForEach(e => { e.Title = null; e.Title2 = null; e.Gender = null; }); } // Add created entities DataTable entitiesTable = DataTable(entities, addIndex: true); var entitiesTvp = new SqlParameter("Entities", entitiesTable) { TypeName = $"dbo.{nameof(AgentForSave)}List", SqlDbType = SqlDbType.Structured }; // The agent type var agentTypeParameter = new SqlParameter("AgentType", agentType); string saveSql = $@" -- Procedure: AgentsForSave SET NOCOUNT ON; DECLARE @IndexedIds [dbo].[IndexedIdList]; DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId')); DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET(); DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId')); -- Deletions DELETE FROM [dbo].[Custodies] WHERE [Id] IN (SELECT [Id] FROM @Entities WHERE [EntityState] = N'Deleted'); INSERT INTO @IndexedIds([Index], [Id]) SELECT x.[Index], x.[Id] FROM ( MERGE INTO [dbo].[Custodies] AS t USING ( SELECT [Index], [Id], [Name], [Name2], [Code], [Address], [BirthDateTime], [IsRelated], [TaxIdentificationNumber], [Title], [Title2], [Gender] FROM @Entities WHERE [EntityState] IN (N'Inserted', N'Updated') ) AS s ON (t.Id = s.Id) WHEN MATCHED THEN UPDATE SET t.[Name] = s.[Name], t.[Name2] = s.[Name2], t.[Code] = s.[Code], t.[Address] = s.[Address], t.[BirthDateTime] = s.[BirthDateTime], t.[IsRelated] = s.[IsRelated], t.[TaxIdentificationNumber] = s.[TaxIdentificationNumber], t.[Title] = s.[Title], t.[Title2] = s.[Title2], t.[Gender] = s.[Gender], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId WHEN NOT MATCHED THEN INSERT ([TenantId], [CustodyType], [Name], [Name2], [Code], [Address], [BirthDateTime], [AgentType], [IsRelated], [TaxIdentificationNumber], [Title], [Title2], [Gender], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]) VALUES (@TenantId, 'Agent', s.[Name], s.[Name2], s.[Code], s.[Address], s.[BirthDateTime], @AgentType, s.[IsRelated], s.[TaxIdentificationNumber], s.[Title], [Title2], s.[Gender], @Now, @UserId, @Now, @UserId) OUTPUT s.[Index], inserted.[Id] ) AS x; "; // Optimization if (!(args.ReturnEntities ?? false)) { // IF no returned items are expected, simply execute a non-Query and return an empty list; await _db.Database.ExecuteSqlCommandAsync(saveSql, entitiesTvp, agentTypeParameter); return(new List <M.Agent>()); } else { // If returned items are expected, append a select statement to the SQL command saveSql = saveSql += "SELECT * FROM @IndexedIds;"; // Retrieve the map from Indexes to Ids var indexedIds = await _db.Saving.FromSql(saveSql, entitiesTvp, agentTypeParameter).ToListAsync(); //// Load the entities using their Ids //DataTable idsTable = DataTable(indexedIds.Select(e => new { e.Id }), addIndex: false); //var idsTvp = new SqlParameter("Ids", idsTable) //{ // TypeName = $"dbo.IdList", // SqlDbType = SqlDbType.Structured //}; // var q = _db.Agents.FromSql("SELECT * FROM dbo.[Custodies] WHERE Id IN (SELECT Id FROM @Ids)", idsTvp); var ids = indexedIds.Select(e => e.Id); var q = _db.Agents.Where(e => ids.Contains(e.Id)); q = Expand(q, args.Expand); var savedEntities = await q.ToListAsync(); // SQL Server does not guarantee order, so make sure the result is sorted according to the initial index Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index); var sortedSavedEntities = new M.Agent[savedEntities.Count]; foreach (var item in savedEntities) { int index = indices[item.Id]; sortedSavedEntities[index] = item; } // Return the sorted collection return(sortedSavedEntities.ToList()); } }
public async Task <ActionResult <SaveSettingsResponse> > Save([FromBody] SettingsForSave settingsForSave, [FromQuery] SaveArguments args) { // Authorized access (Criteria are not supported here) var updatePermissions = await _repo.UserPermissions(Constants.Update, "settings"); if (!updatePermissions.Any()) { return(StatusCode(403)); } try { // Trim all string fields just in case settingsForSave.TrimStringProperties(); // Validate ValidateAndPreprocessSettings(settingsForSave); if (!ModelState.IsValid) { return(UnprocessableEntity(ModelState)); } // Persist await _repo.Settings__Save(settingsForSave); // Update the settings cache var tenantId = _tenantIdAccessor.GetTenantId(); var settingsForClient = await LoadSettingsForClient(_repo); _settingsCache.SetSettings(tenantId, settingsForClient); // If requested, return the updated entity if (args.ReturnEntities ?? false) { // If requested, return the same response you would get from a GET var res = await GetImpl(new GetByIdArguments { Expand = args.Expand }); var result = new SaveSettingsResponse { Entities = res.Entities, Result = res.Result, SettingsForClient = settingsForClient }; return(result); } else { return(Ok()); } } catch (Exception ex) { _logger.LogError($"Error: {ex.Message} {ex.StackTrace}"); return(BadRequest(ex.Message)); } }
protected override async Task <List <M.MeasurementUnit> > PersistAsync(List <MeasurementUnitForSave> entities, SaveArguments args) { // Add created entities DataTable entitiesTable = DataTable(entities, addIndex: true); var entitiesTvp = new SqlParameter("Entities", entitiesTable) { TypeName = $"dbo.{nameof(MeasurementUnitForSave)}List", SqlDbType = SqlDbType.Structured }; string saveSql = $@" -- Procedure: MeasurementUnitsSave SET NOCOUNT ON; DECLARE @IndexedIds [dbo].[IndexedIdList]; DECLARE @TenantId int = CONVERT(INT, SESSION_CONTEXT(N'TenantId')); DECLARE @Now DATETIMEOFFSET(7) = SYSDATETIMEOFFSET(); DECLARE @UserId INT = CONVERT(INT, SESSION_CONTEXT(N'UserId')); INSERT INTO @IndexedIds([Index], [Id]) SELECT x.[Index], x.[Id] FROM ( MERGE INTO [dbo].MeasurementUnits AS t USING ( SELECT [Index], [Id], [Code], [UnitType], [Name], [Name2], [UnitAmount], [BaseAmount] FROM @Entities WHERE [EntityState] IN (N'Inserted', N'Updated') ) AS s ON (t.Id = s.Id) WHEN MATCHED THEN UPDATE SET t.[UnitType] = s.[UnitType], t.[Name] = s.[Name], t.[Name2] = s.[Name2], t.[UnitAmount] = s.[UnitAmount], t.[BaseAmount] = s.[BaseAmount], t.[Code] = s.[Code], t.[ModifiedAt] = @Now, t.[ModifiedById] = @UserId WHEN NOT MATCHED THEN INSERT ([TenantId], [UnitType], [Name], [Name2], [UnitAmount], [BaseAmount], [Code], [CreatedAt], [CreatedById], [ModifiedAt], [ModifiedById]) VALUES (@TenantId, s.[UnitType], s.[Name], s.[Name2], s.[UnitAmount], s.[BaseAmount], s.[Code], @Now, @UserId, @Now, @UserId) OUTPUT s.[Index], inserted.[Id] ) As x OPTION(RECOMPILE) "; // Optimization if (!(args.ReturnEntities ?? false)) { // IF no returned items are expected, simply execute a non-Query and return an empty list; await _db.Database.ExecuteSqlCommandAsync(saveSql, entitiesTvp); return(new List <M.MeasurementUnit>()); } else { // If returned items are expected, append a select statement to the SQL command saveSql = saveSql += "SELECT * FROM @IndexedIds;"; // Retrieve the map from Indexes to Ids var indexedIds = await _db.Saving.FromSql(saveSql, entitiesTvp).ToListAsync(); // Load the entities using their Ids DataTable idsTable = DataTable(indexedIds.Select(e => new { e.Id }), addIndex: false); var idsTvp = new SqlParameter("Ids", idsTable) { TypeName = $"dbo.IdList", SqlDbType = SqlDbType.Structured }; var q = _db.MeasurementUnits.FromSql("SELECT * FROM dbo.[MeasurementUnits] WHERE Id IN (SELECT Id FROM @Ids)", idsTvp); q = Expand(q, args.Expand); var savedEntities = await q.ToListAsync(); // SQL Server does not guarantee order, so make sure the result is sorted according to the initial index Dictionary <int, int> indices = indexedIds.ToDictionary(e => e.Id, e => e.Index); var sortedSavedEntities = new M.MeasurementUnit[savedEntities.Count]; foreach (var item in savedEntities) { int index = indices[item.Id]; sortedSavedEntities[index] = item; } // Return the sorted collection return(sortedSavedEntities.ToList()); } }
public virtual async Task <ActionResult <EntitiesResponse <TEntity> > > Save([FromBody] List <TEntityForSave> entities, [FromQuery] SaveArguments args) { // Note here we use lists https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1?view=netcore-2.1 // since the order is semantically relevant for reporting validation errors return(await ControllerUtilities.InvokeActionImpl(async() => { var result = await SaveImplAsync(entities, args); return Ok(result); }, _logger)); }
protected override Task <(List <ViewForQuery>, IQueryable <ViewForQuery>)> PersistAsync(List <ViewForSave> entities, SaveArguments args) { // TODO throw new NotImplementedException(); }
protected override Task <List <M.Translation> > PersistAsync(List <TranslationForSave> entities, SaveArguments args) { throw new NotImplementedException(); }
/// <summary> /// Verifies that the user has sufficient permissions to update he list of entities provided, this implementation /// assumes that the view has permission levels Read and Update only, which most entities /// </summary> protected virtual async Task CheckUpdatePermissions(List <TDtoForSave> entities, SaveArguments args) { var updatePermissions = await UserPermissions(PermissionLevel.Update); if (!updatePermissions.Any()) { // User has no permissions on this table whatsoever, forbid throw new ForbiddenException(); } else if (updatePermissions.Any(e => string.IsNullOrWhiteSpace(e.Criteria))) { // User has unfiltered update permission on the table => proceed return; } else { // User can update items under certain conditions, so we check those conditions here IEnumerable <string> criteriaList = updatePermissions.Select(e => e.Criteria); // The parameter on which the expression is based var eParam = Expression.Parameter(typeof(TModel)); // Prepare the lambda Expression whereClause = ToORedWhereClause <TModel>(criteriaList, eParam); var lambda = Expression.Lambda <Func <TModel, bool> >(whereClause, eParam); /////// Part (1) Permissions must allow manipulating the original data before the update var existingItems = entities.Where(e => e.EntityState == EntityStates.Updated || e.EntityState == EntityStates.Deleted); if (existingItems.Any()) { await CheckPermissionsForOld(existingItems.Select(e => e.Id), lambda); } /////// Part (2) Permissions must work for the new data after the update, only for the modified properties var newItems = entities.Where(e => e.EntityState == EntityStates.Inserted || e.EntityState == EntityStates.Updated); if (newItems.Any()) { await CheckPermissionsForNew(newItems, lambda); } } }
public async Task <ActionResult <SaveSettingsResponse> > Save([FromBody] SettingsForSave settingsForSave, [FromQuery] SaveArguments args) { // Authorized access (Criteria are not supported here) var updatePermissions = await ControllerUtilities.GetPermissions(_db.AbstractPermissions, PermissionLevel.Update, "settings"); if (!updatePermissions.Any()) { return(StatusCode(403)); } try { // Trim all string fields just in case settingsForSave.TrimStringProperties(); // Validate ValidateAndPreprocessSettings(settingsForSave); if (!ModelState.IsValid) { return(UnprocessableEntity(ModelState)); } // Persist M.Settings mSettings = await _db.Settings.FirstOrDefaultAsync(); if (mSettings == null) { // This should never happen return(BadRequest("Settings have not been initialized")); } _mapper.Map(settingsForSave, mSettings); mSettings.ModifiedAt = DateTimeOffset.Now; mSettings.ModifiedById = _tenantInfo.GetCurrentInfo().UserId.Value; mSettings.SettingsVersion = Guid.NewGuid(); // prompts clients to refresh await _db.SaveChangesAsync(); // If requested, return the updated entity if (args.ReturnEntities ?? false) { // If requested, return the same response you would get from a GET var res = await GetImpl(new GetByIdArguments { Expand = args.Expand }); var result = new SaveSettingsResponse { CollectionName = res.CollectionName, Entity = res.Entity, RelatedEntities = res.RelatedEntities, SettingsForClient = await GetForClientImpl() }; return(result); } else { return(Ok()); } } catch (Exception ex) { _logger.LogError($"Error: {ex.Message} {ex.StackTrace}"); return(BadRequest(ex.Message)); } }
/// <summary> /// Saves the entities (Insert or Update) into the database after authorization and validation /// </summary> /// <returns>Optionally returns the same entities in their persisted READ form</returns> protected virtual async Task <EntitiesResponse <TDto> > SaveImplAsync(List <TDtoForSave> entities, SaveArguments args) { // TODO Authorize POST await CheckUpdatePermissions(entities, args); // Trim all strings as a preprocessing step entities.ForEach(e => TrimStringProperties(e)); using (var trx = await BeginSaveTransaction()) { try { // Validate await ValidateAsync(entities); if (!ModelState.IsValid) { throw new UnprocessableEntityException(ModelState); } // Save var memoryList = await PersistAsync(entities, args); // Flatten related entities and map each to its respective DTO var relatedEntities = FlattenRelatedEntities(memoryList, args.Expand); // Map the primary result to DTOs as well var resultData = Map(memoryList); // Prepare the result in a response object var result = new EntitiesResponse <TDto> { Data = resultData, RelatedEntities = relatedEntities, CollectionName = GetCollectionName(typeof(TDto)) }; // Commit and return trx.Commit(); return(result); } catch (Exception ex) { // Roll back the transaction trx.Rollback(); throw ex; } } }
protected override Task <List <ViewDefinition> > PersistAsync(List <ViewForSave> entities, SaveArguments args) { throw new NotImplementedException(); }
/// <summary> /// Saves <paramref name="settingsForSave"/> as per the specifications in <paramref name="args"/> /// after authorization. /// </summary> /// <param name="settingsForSave">The settings to save.</param> /// <param name="args">The specifications of the save operation.</param> /// <returns>Optionally returns the new READ settings and the new <see cref="SettingsForClient"/>.</returns> public async Task <(TSettings, Versioned <SettingsForClient>)> SaveSettings(TSettingsForSave settingsForSave, SaveArguments args) { await Initialize(); var updatePermissions = await UserPermissions(PermissionActions.Update, cancellation : default); if (!updatePermissions.Any()) { throw new ForbiddenException(); } // Trim all string fields settingsForSave.StructuralPreprocess(); // Attribute Validation var meta = _metadataProvider.GetMetadata(TenantId, typeof(TSettingsForSave), null, null); ValidateEntity(settingsForSave, meta); // Start the transaction using var trx = TransactionFactory.ReadCommitted(); // Persist await SaveExecute(settingsForSave, args); // If requested, return the updated entity TSettings res = default; Versioned <SettingsForClient> newSettingsForClient = default; if (args.ReturnEntities ?? false) { // Get the latest settings for client newSettingsForClient = await _settingsCache.GetSettings( tenantId : TenantId, version : "refresh", // Random string forces a new result from the DB cancellation : default);