Sunday, July 10, 2022

MongoDb Query 2

 

Select necessary fields with skip and take

return await _cSSTemplateCollection
                .AsQueryable().Where(p => p.EventId == eventId &&                                        titile.Content(p.Titile))
                .Select(x => new CssGetVM
                {
                    Id = x.Id,
                    EventId = x.EventId,
                    Title = x.Title,
                    Body = x.Body,
                    CustomBody=x.CustomBody,
                    InsertTimeStamp = x.InsertTimeStamp,
                })
                .Skip(0).Take(25)
                .ToListAsync();

   var RetVal = await (from dbData in _workspaceCollection.AsQueryable<WorkspaceModel>()
  where dbData.OwnerId == ownerid && !dbData.IsDeleted
  && (string.IsNullOrEmpty(title) || dbData.Name.ToLower().Contains(title))
     orderby dbData.InsertTimeStamp descending
   select dbData).Skip((pageNo - 1) * pageSize).Take(pageSize).ToListAsync();


Join

 var dbResult = await (from workspace in _workspaceCollection.AsQueryable<WorkspaceModel>()
                                  where
                                  (string.IsNullOrEmpty(title) || workspace.Name.ToLower().Contains(title))
                                  && !workspace.IsDeleted
                                  join events in _eventsCollection.AsQueryable<EventModel>() on workspace.Id equals events.WorkspaceId into eventdb
                                  from eve in eventdb.DefaultIfEmpty()
                                  select new WorkspacsEvents
                                  {
                                      Id = workspace.Id,
                                      Name = workspace.Name,
                                      Email = workspace.Email,
                                      Description = workspace.Description,
                                      Logo = workspace.Logo,
                                      InsertTimeStamp = workspace.InsertTimeStamp,
                                      IsActive = workspace.IsActive,
                                      OwnerId = workspace.OwnerId,
                                      PackageId = workspace.PackageId,
                                      IsDeleted = workspace.IsDeleted,
                                      WorkspaceEventStatus = new EventStatus
                                      {
                                          EventId = eve.Id,
                                          Status = eve.Status.Name
                                      }

                                  }).ToListAsync();


Select Many

          var query = _workspaceCollection.
                            AsQueryable().
                            Where(x => x.Id == workSpaceId).
                            SelectMany(x => x.PaymentHistory).
                            Where(x => x.IsActive).
                            Select(x => x.Package);


Where

  1. .Where(x => !restrictedEmails.Contains(x.Id))
  2. .Where(x => x.IsActive && x.Title.ToLower().Contains(title))

  3. .Where(x =>(!string.IsNullOrEmpty(x.Translations[nameof(x.Title)][languageCode])  && x.Translations[nameof(x.Title)][languageCode].ToLower().Contains(name))
    |
    |                       
    (string.IsNullOrEmpty(x.Translations[nameof(x.Title)][languageCode])
     && x.Title.ToLower().Contains(name)))

  4. .Where(x => x.AccessDate.End >= DateTime.UtcNow).Where(x => x.AccessDate.Start <= DateTime.UtcNow)
  5. .OrderByDescending(x => x.InsertTimeStamp)
  6.  .Where(x => x.Email == email.ToLowerInvariant())
  7. Query where inner array 
    where !user.IsDeleted
     && user.EventsConfigurations.Any(item => item.EventId == eventId && !item.IsDeleted)
  8. _userCollection.AsQueryable().
     Where(user => user.EventsConfigurations.Any(item => item.EventId == eventId && !item.IsDeleted )). Where(item => !item.IsDeleted);

  9. normallizeNameQuery.OrderBy(x => x.LowerCaseFirstName)
    .ThenBy(x => x.LowerCaseLastName)
    .
    ThenByDescending(x => x.Date)

  10.    Where(x => x.EventsConfigurations.Any(x => eventIds.Contains(x.EventId) && !x.IsWorkspaceTeamMember)).
                    Where(x => x.FirstName.Contains(searchDto.Search) ||
                               x.LastName.Contains(searchDto.Search) ||
                               x.Email.Contains(searchDto.Search) ||
                               x.EventsConfigurations.Any(e => eventIds.Contains(e.EventId) && !e.IsDeleted
                               && e.PersonalInformation.JobTitle.Contains(searchDto.Search))).


Select

01) var allowedAdmins = await _workspaceCollection.
                         AsQueryable().
                         Where(x => x.Id == workspaceId).
                         Select(x => x.PaymentHistory.Where(y => y.IsActive).First().Package.Admins).
                         FirstOrDefaultAsync(ct);

02)   await query.Select(user => new UserResultViewModel
            {
                EventsConfigurations = user.EventsConfigurations.Where(item => item.EventId == eventId).First(),
            }).

03)  var result = dbInfo.Select(x => new AttendeeDto
            {
                JobTitle = x?.EventsConfigurations.FirstOrDefault()?.PersonalInformation?.JobTitle,
                IsProfileVisible = x.EventsConfigurations.FirstOrDefault().IsProfileVisible,
            }).ToList();

Utility

  1.  ObjectId.GenerateNewId().ToString()
  2. public ObjectId Id { get; set; }
  3. [BsonElement("fn")] public string FirstName { get; set; }


Update


var fb = Builders<UserModel>.Filter;
var filter = fb.Where(item => item.Id == userId) 
&
fb.ElemMatch(item => item.EventsConfigurations, e => e.EventId == eventId);


 var updateDef = Builders<UserModel>.Update
                    .Set(x => x.FirstName, dto.FirstName)
                    .Set(user => user.EventsConfigurations[-1].PersonalInformation.Company, dto.Company) 
                    .Set(user => user.EventsConfigurations[-1].PersonalInformation.Description)
                .AddToSet(x => x.AppRoles, AppRoles.Organizer))     
 
 var result = await _userCollection.UpdateOneAsync(filter, updateDef);


--------------

 await _userCollection.UpdateOneAsync(x => x.Id == userId && !x.IsDeleted,
                 Builders<UserModel>.Update.Set(x => x.PhotoPath, input.PhotoPath));

-------------------------

 var fb = Builders<UserModel>.Filter;
            var ub = Builders<UserModel>.Update;

            var updates =
                    users.Select(x =>
                    {
                        var update = ub.AddToSet(x => x.EventsConfigurations, x.Configuration);
                        return new UpdateOneModel<UserModel>(fb.Where(user => user.Id == x.UserId), update);
                    });

            await _userCollection.BulkWriteAsync(updates);



Bulk Update




     var associationsWrites2 = reorder.Resources.Select(
                             update => new UpdateOneModel<PlaylistResource>(Builders<PlaylistResource>.Filter.Eq(p => p.Id, update.Id) &
                             Builders<PlaylistResource>.Filter.Eq(p => p.EventId, reorder.EventId),
                              Builders<PlaylistResource>.Update.Set(x => x.ViewOrder, update.ViewOrder))
                             ).ToList();

                await _playlistResourcesCollection.BulkWriteAsync(associationsWrites2);

two level nested element update

 private async Task UpdateEventLayoutMyProfileComponent(string eventId, SingleVlaueVM input)
        {

            var fb = Builders<EventModel>.Filter;
            var filter = fb.Where(item => item.Id == eventId && item.EventLayout.Pages.Any(p => p.Name == "My profile"
            && p.Components.Any(p2 => p2.Name == "ng-my-profile")));

            var headerSettings = JsonConvert.DeserializeObject<dynamic>(input.Value);

            var profileLayoutSetting = await eventsCollection.AsQueryable()
                .Where(p => p.Id == eventId)
                .Select(p => p.EventLayout.Pages.First(p2 => p2.Name == "My profile"))
                .FirstOrDefaultAsync();

            if (profileLayoutSetting == null)
                return;
            var profileComponent = profileLayoutSetting.Components.FirstOrDefault(p => p.Name == "ng-my-profile");
            if (profileComponent == null)
                return;
            dynamic profileComponentSettings = JsonConvert.DeserializeObject<dynamic>(profileComponent.Settings);
            profileComponentSettings.soapbox = headerSettings.soapBox;
            profileComponentSettings.introvideo = headerSettings.helloWorld;

            string jsonValue = JsonConvert.SerializeObject(profileComponentSettings);

            profileComponent.Settings = jsonValue;

            var updateDef = Builders<EventModel>.Update
               .Set(x => x.EventLayout.Pages[-1].Components, profileLayoutSetting.Components);

            await eventsCollection.UpdateOneAsync(filter, updateDef);
        }

-----------
Add an element to an array

var filter = Builders<SupportTicketModel>.Filter.Where(x => x.Id == ticketId);
            var ub = Builders<SupportTicketModel>.Update.AddToSet(ticket => ticket.Comments, input);
            var result = await Collection.UpdateOneAsync(filter, ub);


------------

        var filter = Builders<EventModel>.Filter;

            var updateFilter = filter.And(
              filter.Eq(x => x.Id, eventId),
              filter.ElemMatch(x => x.EventLayout.UserMenu, c => c.Id == input.Id));

            var updateDef = Builders<EventModel>.Update
                .Set("EventLayout.UserMenu.$.Label", input.Label)
                .Set("EventLayout.UserMenu.$.PageId", input.PageId)
                .Set("EventLayout.UserMenu.$.IsPublished", input.IsPublished);

 var retValu = await eventsCollection.UpdateOneAsync(updateFilter, updateDef);


MOngosh

db['event-roles-permissions'].updateMany({RoleId:"62d65d092c3c5108e0975105"},{$set:{Name:"Booth Manager",Description:"Booth Manager Role"}})





Monday, May 30, 2022

MongoDb and C sharp

 Base Repository

https://drive.google.com/file/d/1-2M7YotJQ1Q31XZB32l8nt70wbqnCE7t/view?usp=sharing

Tuesday, May 24, 2022

Mongo db query

 Query

> use s27_events


  1. SELECT * FROM Profile

    db.Profile.find ({})

  2. SELECT * FROM Profile WHERE Name = "Chamith"

    db.Profile.find({Name:"Chamith"})

  3. SELECT * FROM Profile WHERE Name = "Chamith" AND Dob = "1988-01-24" AND Setting.Security._2fa = false

    db.Profile.find({Name:"Chamith",Dob:"1988-01-24",Setting:{Security:{_2fa:false}}})



Update

update/add a new field to the collection


db.Profile.update({},{$set:{ProfileImage:"chamith.png"}})

db.Profile.update({},{$unset:{ProfileImage:"chamith.png"}})

db.Profile.update({},{$set:{ProfileImage:[{Url:"chamith1",IsDefault:false},{Url:"chamith2",IsDefault:true}]}})

db.Profile.update({},{$set:{Setting:{Security:{ _2fa:false}}}})

db.Profile.update({_id:ObjectId('628cae92a72e5e4316a8a8a5')},{$set:{Setting:{Preferences:{Country:"Sri Lanka",Currency:"LKR"},Security:{_2fa:true}}}})




Wednesday, February 24, 2021

Digital signature apply to http request body

 public CheckValiedContent (HttpRequest request){

          string xeroPrivateKey = Environment.GetEnvironmentVariable("XEROKEY");

          var signatureHeader = request.Headers["x-xero-signature"];
          var requestBody = request.Body;

         var resultSignature = GenarateSignature(requestBody,xeroPrivateKey);

         if (signatureHeader != resultSignature){
            return 401
        }

 }


 public string GenerateSignature(string dataToHash, string xeroKey)
        {
            using (var hmac = new HMACSHA256(Encoding.UTF8.GetBytes(xeroKey)))
            {
                var messageBytes = Encoding.UTF8.GetBytes(dataToHash);
                var hash = hmac.ComputeHash(messageBytes);
                return Convert.ToBase64String(hash);
            }
        }

Wednesday, February 10, 2021

Backend best practices

 Select praticulr coloms from database (EF)


https://git.itelasoft.com.au/chamith.saranga/broker-service/blob/master/Application/Services/BankStatementService.cs#L544

return await DataContext.BankStatements
                .Where(s => bankStatementIds.Contains(s.RequestId))
                .Include(s => s.Applicant).Include(s => s.Lender)
                .Select(m => new BankStatementModel
                {
                    RequestId = m.RequestId,
                    ApplicantId = m.ApplicantId,
                    ApplicantName = $"{m.Applicant.FirstName} {m.Applicant.LastName}",
                    IsPrimaryApplicant = m.Applicant.PrimaryApplicant,
                    LenderId = m.LenderId,
                    LenderName = m.Lender != null?m.Lender.Name : m.LenderName,
                    LenderLogo = m.Lender.Logo,
                    Deprecated = m.Deprecated,
                    Provider = m.Provider,
                    RequestVersion = m.RequestVersion
                }).ToListAsync();

EF.Function
https://git.itelasoft.com.au/chamith.saranga/broker-service/blob/master/Application/Services/ProductService.cs#L134
           if (!string.IsNullOrWhiteSpace(name))           
                query = query
                    .Where(p => EF.Functions.Like(p.Name, $@"%{name}%"));          

CS Events