[CSHARP-4818] LINQ GroupJoin Does Not Support $lookup with array of values Created: 24/Oct/23  Updated: 15/Nov/23  Resolved: 15/Nov/23

Status: Closed
Project: C# Driver
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor - P4
Reporter: M. Scott Ford Assignee: Robert Stam
Resolution: Gone away Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

Summary

The `$lookup` aggregation pipeline step supports referencing a local field that contains an array of items. The `GroupJoin` method does not support this scenario, instead requiring that the local field is a single value.

Please provide the version of the driver. If applicable, please provide the MongoDB server version and topology (standalone, replica set, or sharded cluster).

 

This applies to version 2.22.0.

How to Reproduce

Steps to reproduce. If possible, please include a Short, Self Contained, Correct (Compilable), Example.

 

Given the following sample collections:

 

people: [
  {
    _id: new ObjectId("6537c2058a094813badf7b12"),
    name: "Jessie"
    organizations: [new ObjectId("6537c2058a094813badf7b11")]
  }
]
 
organizations: [
  {
    _id: new ObjecId("6537c2058a094813badf7b11"),
    name: "Sample"
  }
]

 

 

I can write the following aggregation pipeline:

 

db.people.aggregate([
    {
        $lookup: {
            from: "organizations",
            localField: "organizations",
            foreignField: "_id",
            as: "organizations"
        }
    }
]) 

 

 

And retrieve a result that looks like:

 

[
  {
    _id: new ObjectId("6537c2058a094813badf7b12"),
    name: "Jessie"
    organizations: [
      {
        _id: new ObjectId("6537c2058a094813badf7b11"),
        name: "Sample"
      }
    ]
  }
]

 

Using LINQ, I would expect to be able to do the following:

class PersonDto 
{
    public ObjectId Id { get; set; }
    public string Name { get; set; }
    public List<ObjectId> Organizations { get; set; }
}
 
class Organization
{
    public ObjectId { get; set; }
    public string Name { get; set; }
}
 
class Person
{
    public ObjectId { get; set; }
    public string Name { get; set; }
    public List<Organization> Organizations { get; set; }
}
 
// This snippet below assumes the presence of the classes above
 
var peopleCollection = mongoDbClient.GetDatabase("example").GetCollection<PersonDto>("people");
var organizationsCollection = mongoDbClient.GetDatabase("example").GetCollection<PersonDto>("organizations");
 
var person = await peopleCollection.AsQueryable()
    .GroupJoin<PersonDto, Organization, ObjectId, Person>(organizationsCollection.AsQueryable(),
        // compilation error on this line: Cannot convert expression 
        // type 'System.Collections.Generic.List<MongoDB.Bson.ObjectId>' 
        // to return type 'MongoDB.Bson.ObjectId'
        person => person.Organizations,
        organization => organization.Id,
        (person, organizations) => new Person {
            Id = person.Id,
            Name = person.Name,
            Organizations = organizations.ToList()
         }
    )
    .FirstOrDefaultAsync();

 

Additional Background

Please provide any additional background information that may be helpful in diagnosing the bug.



 Comments   
Comment by PM Bot [ 15/Nov/23 ]

There hasn't been any recent activity on this ticket, so we're resolving it. Thanks for reaching out! Please feel free to reopen this ticket if you're still experiencing the issue, and add a comment if you're able to provide more information.

Comment by PM Bot [ 07/Nov/23 ]

Hi scott@freshli.io! CSHARP-4818 is awaiting your response.

If this is still an issue for you, please open Jira to review the latest status and provide your feedback. Thanks!

Comment by Robert Stam [ 30/Oct/23 ]

The reason you are getting a compile time error is that LINQ requires a scalar expression for the ` outerKeySelector` argument to `GroupJoin`, and `x.Organizations` is an enumerable value.

I don't think it's possible to do what you are trying to do directly in LINQ because of this. However, you could use LINQ to unwind the `Organizations` property and do a regular `Join` and then use `GroupBy` to populate the `Organizations` property of the final `Person` result.

The following worked for me:

var queryable = peopleCollection.AsQueryable()
    .SelectMany(x => x.OrganizationIds.Select(
        organizationId => new PersonDtoUnwound
        {
            Id = x.Id,
            Name = x.Name,
            OrganizationId = organizationId
        }))
    .Join(
        organizationsCollection.AsQueryable(),
        person => person.OrganizationId,
        organization => organization.Id,
        (person, organization) => new PersonUnwound
        {
            Id = person.Id,
            Name = person.Name,
            Organization = organization
        })
    .GroupBy(
        x => x.Id,
        (key, unwound) => new Person
        {
            Id = key,
            Name = unwound.First().Name,
            Organizations = new List<Organization>(unwound.Select(p => p.Organization))
        });

I needed the following intermediate classes as well:

class PersonDtoUnwound
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int OrganizationId { get; set; }
}
 
class PersonUnwound
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Organization Organization { get; set; }
}

Comment by PM Bot [ 24/Oct/23 ]

Hi scott@freshli.io, thank you for reporting this issue! The team will look into it and get back to you soon.

Generated at Wed Feb 07 21:49:27 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.