How to query nested list with MongoDB C# Driver projections-CodePudding

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.

CodePudding user response：

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