MongoDB - Count unread messages

Question

I try to count unread messages for a user.

On my model, I have a property, LastMessageDate that contains the date on the last created message in the group chat. I have also a Members property (list) that contains the members in the group chat. Each member has the UserId and LastReadDate properties. The LastReadDate is updated when the user writes a new message in the group chat or when the user loads messages from the group chat.

Now I want to count the number of chats where a specific user has unread messages (The messages are stored in another collection). I try this:

var db = GetGroupCollection();

var filter = Builders<ChatGroup>.Filter.Where(p => p.Members.Any(m => m.UserId == userId && m.LastReadDate < p.LastMessageDate));
return await db.CountDocumentsAsync(filter);

But I receive the following error:

The LINQ expression: {document}{Members}.Where((({document}{UserId} == 730ddbc7-5d03-4060-b9ef-2913d0b1d7db) AndAlso ({document}{LastReadDate} < {document}{LastMessageDate}))) has the member "p" which can not be used to build a correct MongoDB query.

What should I do? Is there a better solution?

As I know, currently it's not supported to use parent linq income argument (p) in nested linq queries, you can try a raw query in form like: await db.CountDocumentsAsync("your query in a raw json/bson form ") — dododo
– dododo, Commented Jul 13, 2022 at 15:29

Yong Shun · Accepted Answer · 2022-07-14 01:30:03Z

4

Based on the provided data in the comment, I think the aggregation query is required to achieve the outcome.

$set - Set Members field

1.1. $filter - With Members array as input, filter the document(s) with matching the current document's UserId and LastMessageDate is greater than ($gt) the current document's LastReadDate.
$match - Filter the document with Members is not an empty array.

db.groups.aggregate([
  {
    "$set": {
      Members: {
        $filter: {
          input: "$Members",
          cond: {
            $and: [
              {
                $eq: [
                  "$$this.UserId",
                  1
                ]
              },
              {
                $gt: [
                  "$LastMessageDate",
                  "$$this.LastReadDate"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    $match: {
      Members: {
        $ne: []
      }
    }
  }
])

Sample Mongo Playground

For C# syntax, either you can directly provide the query as a string or convert the query to BsonDocument syntax.

Note that the query above will return the array of documents, hence you will need to use System.Linq to count the returned document(s).

using System.Linq;

var pipeline = new BsonDocument[]
{
    new BsonDocument("$set", 
        new BsonDocument("Members", 
            new BsonDocument("$filter", 
                new BsonDocument
                { 
                    { "input", "$Members" },
                    { "cond", new BsonDocument
                        (
                            "$and", new BsonArray
                            {
                                new BsonDocument("$eq", 
                                    new BsonArray { "$$this.UserId", userId }),
                                new BsonDocument("$gt",
                                    new BsonArray { "$LastMessageDate", "$$this.LastReadDate" })
                            }
                        )
                    }
                }
            )
        )
    ),
    new BsonDocument("$match",
        new BsonDocument("Members",
            new BsonDocument("$ne", new BsonArray())))

};

var db = GetGroupCollection();

return (await db.AggregateAsync<BsonDocument>(pipeline))
    .ToList()
    .Count;

answered Jul 14, 2022 at 1:30

Yong Shun

54.5k6 gold badges38 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mrcode Over a year ago

thanks, works perfect. One thing, this query will return all matching documents to the c# code, then the c# code will count the items/list? If so, is it posible to return only the number of the items?

Yong Shun Over a year ago

this query will return all matching documents to the c# code, then the c# code will count the items/list? Yes, the C# side does the counting. If so, is it posible to return only the number of the items? Did you mean MongoDB side to return count only? If yes, I afraid that it is not possible for MongoDB so far.

Yong Shun Over a year ago

Performance-wise, since you are performing count, you return the document with _id field only and omit the rest field(s) via $project from MongoDB side.

Nadav Hury · Accepted Answer · 2022-07-13 17:03:29Z

0

When you want to query a nested list of a document, ElemMatch is your solution, Try

var filter = builder.ElemMatch(o => o.Members,m => m.UserId == userId && m.LastReadDate < p.LastMessageDate);

edited Jul 13, 2022 at 17:03

answered Jul 13, 2022 at 16:58

Nadav Hury

7276 silver badges13 bronze badges

6 Comments

mrcode Over a year ago

I can't access o.LastMessageDate, Error CS0103 The name 'o' does not exist in the current context

Nadav Hury Over a year ago

First parameter of the ElemMatch function accepts the members array, that's the o. Second parameter is the lambda > m => m.UserId == userId && m.LastReadDate < p.LastMessageDate

mrcode Over a year ago

Where do you get p in p.LastMessageDate from?

Nadav Hury Over a year ago

After taking another look, I think the problem is that you query a nested list based on a value on the document that holds it. when you ask for m.LastReadDate < p.LastMessageDate the m is the nested list item, and the p is the document that holds it. it is not possible with the solution I gave you.

Nadav Hury Over a year ago

Try using aggregation, you want to have the LastMessageDate field projected into the ElemMatch query, and then you will be able to use it. Take a look here stackoverflow.com/questions/45412506/…

|

Collectives™ on Stack Overflow

MongoDB - Count unread messages

2 Answers 2

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related