MongoDB C# Fluent Aggregate

Question

I'm trying to use the aggregate pipeline with fluent interfaces with no success, though I'm not receiving any error (all fields from result are null).

I have this User class:

public class User
{
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id;

    ...

    [BsonElement("last_access")]
    public DateTime LastAccess;
}

Entity class:

public class Entity
{
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id;

    ...

    [BsonElement("active")]
    public bool Active;

    [BsonElement("user_id")]
    public string UserId;
}

UserLookup class. This is used for the $lookup.

class UserLookup
{
    public int EntityCount;

    public IEnumerable<User> UsersData;
}

UserResult class. This is used for group and projection.

class UserResult
{
    public string UserId;

    public int EntityCount;

    public User UserData;
}

In my function, I have something like this:

IMongoCollection<Entity> entityCol = Database.Instance.GetCollection<Entity>("entities");
IMongoCollection<User> usersCol = Database.Instance.GetCollection<User>("users");

IAsyncCursor<UserResult> result = entityCol.Aggregate()
    .Match(e => e.Active)
    .Group(e => e.UserId, g => new UserResult {
        UserId = g.Key,
        EntityCount = g.Count()
    })
    .Lookup<UserResult, User, UserLookup>(usersCol,
        lf => lf.UserId,  // localField. UserResult.UserId
        ff => ff.Id,      // foreignField. User.Id
        r => r.UsersData  // result. UserLookup.UsersData
    )
    .Project(p => new UserResult {
        UserId = p.UserId,
        EntityCount = p.EntityCount,
        UserData = p.UsersData.First()
    })
    .ToCursor();

while (result.MoveNext()) {
    foreach (var ur in result.Current) {
        // ur.UserId = null; ur.UserData = null; ur.EntityCount = 0;
    }
}

I don't receive any error, but EntityCount is always 0 and both UserId and UserData are null. Basically, what I want is:

Get all entities that are active (Match).
Group them by user id (Group).
Lookup in the users collection to get the user data (Lookup).
Project the result to return a simple object with entity count and the user data (Project).

----- Update 1

Ok, after playing with mongo shell, I think I found the problem. It seems the mongo can't find entries by id with ObjectId, only with strings. This is weird, I found this answer and it seems it's possible to find using ObjectId (at least in the past).

In mongo shell, if I use db.users.find({ _id: ObjectId("...") }) it returns nothing, but with db.users.find({ _id: "..." }) it returns the expected user.

I wrote that aggregate query from scratch to run on shell, here it is:

db.entities.aggregate([
    {
        $match: {
            "active": "true",
        }
    },
    {
        $group: {
            "_id" : {
                $toString: "$user_id"
            },
            "EntityCount": { "$sum" : 1 }
        }
    },
    {
        $lookup: {
            from: "users",
            localField: "_id",
            foreignField: "_id",
            as: "UsersData"
        }
    },
    {
        $project: {
            "_id": "$_id",
            "EntityCount": "$EntityCount",
            "UserData": {
                "$arrayElemAt": ["$UsersData", 0]
            },
        }
    },
    { $limit: 2 }
])

Note in the $group stage that I'm converting the user id to string. Won't work if I use "_id": "$user_id".

The last stage $limit is just there to not blow out the console, making it easier to read the result.

This query executes perfectly fine.

Back to C#

This is the final query that C# driver uses:

[
    {
        "$match": {
            "active": true,
        }
    },
    {
        "$group": {
            "_id": "$user_id",
            "EntityCount": {
                "$sum":1
            }
        }
    },
    {
        "$lookup": {
            "from": "users",
            "localField": "_id",
            "foreignField": "_id",
            "as": "users_data"
        }
    },
    {
        "$project": {
            "UserId": "$user_id",
            "EntityCount": "$EntityCount",
            "UserData": {
                "$arrayElemAt": ["$user_data", 0]
            },
            "_id": 0
        }
    }
]

I don't know why, but at the $group stage, the UserId field it's being ignored (this explains why it's always null in the result). Also, you can note that the _id is being set to 0 in the $lookup stage.

I renamed the field UserId from UserResult to Id and added the attribute [BsonElement("_id")].

Now,I get both user id and entity count in the result, but the UserData is still null.

Have you established a Mongodb connection to the MongoDB data? And are you using the Newtonsoft.Json.Bson and MongoDB libraries? — Iliass Akkara
– Iliass Akkara, Commented May 4, 2020 at 21:12
@IliassNassibane The connection is fine. The nuget version of MongoDB.Driver is 2.9.2. My mongo server is 4.2.3. All my operations is running fine, except for this aggregate. — Kiritonito
– Kiritonito, Commented May 4, 2020 at 21:32

ntohl · Accepted Answer · 2020-05-13 15:31:06Z

1

Query form, that works>

IEnumerable<UserResult> result = entityCol.AsQueryable().Where(x => x.Active).ToLookup(x => x.UserId)
    .Select(x => new UserResult {EntityCount = x.Count(), UserId = x.Key}).Join(usersCol.AsQueryable(),
        x => x.UserId, x => x.Id,
        (userResult, user) => new UserResult
            {EntityCount = userResult.EntityCount, UserData = user, UserId = userResult.UserId});

foreach (var ur in result)
{
    // ur.UserId = null; ur.UserData = null; ur.EntityCount = 0;
}

Your suspection for ObjectId - string conversion not working in Grouping is correct.

this works:

public class User
{
    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id;

    [BsonElement("last_access")]
    public DateTime LastAccess;
}

public class Entity
{
    [BsonRepresentation(BsonType.ObjectId)]
    [BsonId]
    public string Id;

    [BsonElement("active")]
    public bool Active;

    [BsonElement("user_id")]
    [BsonRepresentation(BsonType.ObjectId)]
    public string UserId;
}

class UserLookup
{
    public int EntityCount;

    public User[] UsersData;

    [BsonRepresentation(BsonType.ObjectId)]
    public string Id;
}

class UserResult
{
    public string UserId;

    public int EntityCount;

    public User UserData;
}

This way it works>

IAsyncCursor<UserResult> result = entityCol.Aggregate()
    .Match(e => e.Active)
    .Group(e => e.UserId, g => new UserResult
    {
        UserId = g.Key,
        EntityCount = g.Count(),
    })
    .Lookup(usersCol,
        lf => lf.UserId,  // localField. UserResult.UserId
        ff => ff.Id,      // foreignField. User.Id
        (UserLookup r) => r.UsersData  // result. UserLookup.UsersData
    )
    .Project(p => new UserResult
    {
        UserId = p.UsersData.First().Id,
        EntityCount = p.EntityCount,
        UserData = p.UsersData.First()
    })
    .ToCursor();

edited May 13, 2020 at 15:31

answered May 5, 2020 at 15:19

ntohl

2,1251 gold badge30 silver badges35 bronze badges

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

2 Comments

Kiritonito Over a year ago

I need to add entityIds even if I don't need it? I will test later, it's late here. I updated my question with tests with mongo shell.

ntohl Over a year ago

@Kiritonito reworked the query. No need for entityIds where it doesn't belong.

Iliass Akkara · Accepted Answer · 2020-05-04 22:26:27Z

0

I think the issue resides in this

IAsyncCursor<UserResult> result = entity.Aggregate()

It should refer to the entity collection you made, entityCol.

IAsyncCursor<UserResult> result = entityCol.Aggregate()

answered May 4, 2020 at 22:26

Iliass Akkara

6718 silver badges16 bronze badges

1 Comment

Kiritonito Over a year ago

Sorry, my mistake. I'm already using Aggregate from entityCol in my real code (I wrote the code from question from scratch, to make it clean and clear). I will update the code.

Collectives™ on Stack Overflow

MongoDB C# Fluent Aggregate

----- Update 1

Back to C#

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

----- Update 1

Back to C#

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related