Find lastly added collection with some condition in MongoDB and C#

user_17802104 0
Viewed 49

I am working a asp .net 6.0 with mongoDB.

I have a query to filter hotel results.

public async Task<List<HotelResultDocument>> GetHotelResults(List<string> rateKeys)
    {
        var filter = Builders<HotelResultDocument>.Filter.In(x => x.RateKey, rateKeys);

        filter &= Builders<HotelResultDocument>.Filter.Eq(x => x.RateType, "BOOKABLE");

        filter &= Builders<HotelResultDocument>.Filter.Gt(x => x.ExpiredAt, DateTime.Now);

        return await _hotelResultsCollection.Find(filter).ToListAsync();
    }

Here My issue is There are more than one responses for same rateKEy with "BOOKABLE" type. I want only lastly added collection with a rateKEy with "BOOKABLE" type.

How to filter to get above response?

public class HotelResultDocument
    {
        public string Id { get; set; }

        public string Reference { get; set; }

        public string Token { get; set; }

        public int HotelCode { get; set; }

        public string RateKey { get; set; }

        public string RateClass { get; set; }

        public string RateType { get; set; }

        public double Net { get; set; }

        public DateTime CreatedAt { get; set; }

        public DateTime ExpiredAt { get; set; }
    }

Note : If I sent four rateKeys I have to find the hotels for each rateKeys (if the hotel satisfied above conditions.) But using above query I got more then one hotel for a rateKey. I have to find the lastly added hotel for a particular rateKey.

Edit : Current Response


"hotel" : [
{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:50:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:51:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:52:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:54:26},
   "RateKey" : 1,
},

{
   "hotelCode" : 4,
   "CreatedAt" :{26/07/2022 03:52:26},
   "RateKey" : 5,
},

{
   "hotelCode" : 4,
   "CreatedAt" :{26/07/2022 03:55:26},
   "RateKey" : 5,
}
]

Expected Response :

"hotel" : [

{
   "hotelCode" : 2,
   "CreatedAt" :{26/07/2022 03:54:26}, 
   "RateKey" : 1,
},

{
   "hotelCode" : 4,
   "CreatedAt" :{26/07/2022 03:55:26},
   "RateKey" : 5,
}
]

Anyone help me. Thank you.

asp.net-core mongodb-.net-driver mongodb c#
1 Answers

I believe that you can achieve it with Aggregation Pipeline.

Updated based on Post Owner's requirements

  1. $match - Filter documents.

  2. $sort - Order by CreatedAt descending.

  3. $group - Group by RateKey and take the first document as hotel.

  4. $replaceWith - Replace the input document with hotel.

MongoDB query

db.collection.aggregate([
  {
    $match: {}
  },
  {
    $sort: {
      CreatedAt: -1
    }
  },
  {
    $group: {
      _id: "$RateKey",
      hotel: {
        $first: "$$ROOT"
      }
    }
  },
  {
    "$replaceWith": "$hotel"
  }
])

Sample Mongo Playground

MongoDB .NET driver C# syntax

public class GroupedHotelResult
{
    [BsonId]
    public string Id { get; set; }

    [BsonElement("hotel")]
    public HotelResultDocument Hotel { get; set; }
}

SortDefinition<HotelResultDocument> sort =
    Builders<HotelResultDocument>.Sort.Descending(x => x.CreatedAt);

ProjectionDefinition<HotelResultDocument, GroupedHotelResult> group =
    new BsonDocument
    {
        { "_id", "$RateKey" },
        { "hotel", new BsonDocument
            {
                { "$first", "$$ROOT" }
            }
        }
    };

return await _collection
    .Aggregate()
    .Match(filter)
    .Sort(sort)
    .Group<GroupedHotelResult>(group)
    .ReplaceWith<GroupedHotelResult, HotelResultDocument>(x => x.Hotel)
    .ToListAsync();

Output

enter image description here

user_80176900
Related