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
– Yong Shun, Commented Jul 19, 2022 at 0:30

dododo · Accepted Answer · 2022-07-21 14:38:04Z

3

+50

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

answered Jul 21, 2022 at 14:38

dododo

4,7832 gold badges19 silver badges48 bronze badges

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

3 Comments

fhcimolin Over a year ago

I ended up creating a JIRA ticket if anyone is interested in following its progress: jira.mongodb.org/browse/CSHARP-4265

fhcimolin Over a year ago

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.

dododo Over a year ago

it's not old and clunky. These methods use different underlying commands. AsQuerable uses aggregate, Find =>find itself.

Collectives™ on Stack Overflow

How to query nested list with MongoDB C# Driver projections

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related