示例#1
0
        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());
                }
            }
        }
示例#2
0
 public async Task DeleteBlobs(IEnumerable <string> blobNames)
 {
     await _blobService.DeleteBlobs(blobNames);
 }