Sql server row level policy

CS Events

 

Delegate

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"}})

MongoDb and C sharp

 Base Repository

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

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}}}})

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);
            }
        }