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 async Task DeleteBlobs(IEnumerable <string> blobNames)
{
await _blobService.DeleteBlobs(blobNames);
}