How to query nested list with MongoDB C# Driver projections

Question

Suppose I have the following data in my MongoDB:

{
  "_id": {
    "$oid": "62baf9a2851424fdb8c226f8"
  },
  "Name": "Team A",
  "TeamData": {
    "Players": [
      {
        "FirstName": "First Name A",
        "LastName": "Last Name A"
      },
      {
        "FirstName": "First Name B",
        "LastName": "Last Name B"
      }
    ]
  }
}

I only want to query LastName. FirstName should be null.

The query projection looks like this, using dot notation:

{
    "TeamData.Players.LastName": 1
}

How can I do this using the C# driver's LINQ support? I was able to come close with the code below, but not quite.

public IEnumerable<TeamDto> GetTeam()
{
    return _collection.Find(filter).Project(x => new TeamDto
    {
        Id = x.Id,
        TeamData = new TeamDataDto
        {
            Players = x.TeamData.Select(y => new PlayerDto
            {
                LastName = y.LastName
            })
        }
    });
}

The result is: Players array is populated, but with null values. I also tried my hand at Include and Exclude inside .Project(), also to no avail. Can anyone shed a light?

Edit: My question is actually very similar to this one, but using LINQ instead. I know there's a way to put dot notation in .Project, but that's also not want I want. Thanks.

Thinking that the LINQ part is unable to convert to the MongoDB query syntax expression. Either you have to query all the fields and perform mapping in the memory, or use the dot notation that you mentioned. — Yong Shun, Jul 19 '22 at 00:30

score 2 · Accepted Answer · answered Jul 21 '22 at 14:38

This looks like a bug (or at the very least a bit unexpected behavior) to me. Your projection actually works. I've simplified it a bit to this form:

var result = coll.Find("{}")
   .Project(x => x.TeamData.Players.Select(c => c.LastName))
   .ToList();

Actually generated request for this case is:

{
    "find": "coll",
    "filter": {},
    "projection": {
        "Players.LastName": 1,
        "TeamData.Players": 1,
        "_id": 0
    }
}

It's a bit unexpected that this level projection contains "TeamData.Players": 1 that leads to creating result with FirstName even though we didn't configure it:

{
    "TeamData": {
        "Players": [{
                "FirstName": "First Name A",
                "LastName": "Last Name A"
            }, {
                "FirstName": "First Name B",
                "LastName": "Last Name B"
            }
        ]
    }
}

you can check it in the shell as well via:

db.runCommand({ "find" : "coll", "filter" : { }, "projection" : { "Players.LastName" : 1, "TeamData.Players" : 1, "_id" : 0 } })

The issue with the above projection is minor and can be fixed by specifying expected projection directly:

var result = coll.Find("{}")
   .Project("{ 'TeamData.Players.LastName': 1 }")
   .ToList();

that generates just simple expected MQL:

{ "find" : "coll", "filter" : { }, "projection" : { "TeamData.Players.LastName" : 1 } }

that, in turns, returns expected document:

{
    "_id": ObjectId("62d94cdeed9104ede4a35d75"),
    "TeamData": {
        "Players": [{
                "LastName": "Last Name A"
            }, {
                "LastName": "Last Name B"
            }
        ]
    }
}

but the problem is when a server has returned document, the driver also should deserialize this document to the projected output. And the projected output that you configure via LINQ expression is a simple IEnumerable<string>, but from the server POV it should be a list of documents (TeamData, Players), so the driver fails to do it. Since he cannot convert TeamData document to a string, it just returns null. You can see actually returned document via replacing a collection generic type on BsonDocument:

  var coll = db.GetCollection<BsonDocument>("coll");
  var result = coll.Find("{}")
       .Project("{ 'TeamData.Players.LastName': 1 }")
       .ToList();

So the problem is that deserialization of a server response on a driver side is incorrect and this is why your resulted items are with null. I would recommend creating a JIRA ticket here

I ended up creating a JIRA ticket if anyone is interested in following its progress: https://jira.mongodb.org/browse/CSHARP-4265 — fhcimolin, Jul 25 '22 at 11:35
Late update on this, but the syntax I was using was called FindFluent. Apparently it's kind of old and clunky. They suggested I used `.AsQueryable()` and from there use `.Select()` as means of projection. It worked. — fhcimolin, Dec 20 '22 at 13:40
it's not old and clunky. These methods use different underlying commands. AsQuerable uses `aggregate`, Find =>`find` itself. — dododo, Dec 20 '22 at 13:46

How to query nested list with MongoDB C# Driver projections

1 Answers1