How to join multiple collections using Mongodb C# driver

Question

I need to join 3 collections in aggregation with multiple $lookup I tried in C# driver it allow me to $lookup User collection but can't perform second $lookup for Setting collection.

Anyone can help?

db.Transactions.aggregate([
    {
        $lookup:
        {
            from: "Account",
            localField: "AccountId",
            foreignField: "_id",
            as: "Account"
        }
    },
       {
           $lookup:
        {
            from: "User",
            localField: "UserId",
            foreignField: "_id",
            as: "User"
        }
       }
    ])
    .match({
    })
    .project({})

here is the C# code:

 var account = _dbClient.GetDatabase(_dbName).GetCollection<Account>("Accounts");
var user = _dbClient.GetDatabase(_dbName).GetCollection<User>("Users");
var transaction = _dbClient.GetDatabase(_dbName).GetCollection<Transaction>("Transactions");

var result = (from t in transaction.AsQueryable()
              join a in account.AsQueryable() on t.AccountId equals a.Id
              join u in user.AsQueryable() on t.UserId equals u.Id into userList
              from acc in userList.DefaultIfEmpty()
              where acc.CompanyName.ToLower().Contains(companyName) && c.CreatedDate >= fromDate && c.CreatedDate <= toDate
              select new TransactionHistory
              {
                   Id = t.Id,
                   CompanyName = acc.CompanyName,
                   UserId = u.UserId
                   FirstName = u.FirstName
              }).ToList();

I got the error $project or $group does not support {document}. using Linq.

I need to do some filter such as CompanyName.Contains() for Account collection. I tried in Linq but it throws me the message say Containts() is not supported — codecodeNinja
– codecodeNinja, Commented Aug 8, 2019 at 5:24
.Contains(xyz) is certainly supported...not sure about Containts()... — jazb
– jazb, Commented Aug 8, 2019 at 5:26
What do you mean by unable to perform the second $lookup ? Could you post a code snippet, also (if any) errors that you're getting? — Wan B.
– Wan B., Commented Aug 8, 2019 at 6:07

TanvirArjel · Accepted Answer · 2021-04-24 03:59:34Z

I need to join 3 collections in aggregation with multiple $lookup

Given the following classes:

public class Transactions
{
    public ObjectId Id { get; set; }
    public int UserId { get; set; }
    public int AccountId { get; set; }
    public int SettingId { get; set; }
}
public class Account
{
    public int Id {get; set;}
    public int Name {get; set;}
}
public class User
{
    public int Id {get; set;}
    public int Name {get; set;}
}
public class Setting
{
    public int Id {get; set;}
    public int Name {get; set;}
}

You can perform multiple $lookup stage as below using MongoDB .NET/C# driver (currently v2.9):

var collection = database.GetCollection<Transactions>("transactions");

var docs = collection.Aggregate()
                     .Lookup("account", "AccountId", "_id", "asAccounts")
                     .Lookup("user", "UserId", "_id", "asUsers")
                     .Lookup("setting", "SettingId", "_id", "asSettings")
                     .As<BsonDocument>()
                     .ToList();

foreach (var doc in docs) {
    Console.WriteLine(doc.ToJson());
}

You can add a Match, in between/before/after if you would like to filter for specific values. Just keep in mind that the documents after altered after each Lookup stage.

Worth mentioning that if you need to join multiple collections as part of your common operations, you should reconsider the database data model. Please see Schema Design: Summary for more information.

storm_167 · Accepted Answer · 2024-03-21 10:47:38Z

Both worked with C#.Net 8 and Mongo Driver 2.24.0
PS: Both don't support async.
PS2: 2nd one doesnt allow anonymous return type. Type of return object must be explicitly declared.

Solution 1:

IMongoCollection<T> db = database.GetCollection<T>("user");
_companyService.db = database.GetCollection<T>("company");

var result = (from user in db.AsQueryable()
              join company in _companyService.db.AsQueryable() on user.CompanyId equals company.Id into companyJoin
              from company in companyJoin.DefaultIfEmpty()
              join manager in db.AsQueryable() on user.ManagerGuid equals manager.Guid into managerJoin
              from manager in managerJoin.DefaultIfEmpty()
              where user.IsDisabled == false
              select new
              {
                 user.Id,
                 user.Name,
                 user.SurName,
                 CompanyName = company.DisplayName,
                 ManagerName = manager.Name)
              }).ToList();

Solution 2:

var result = db.AsQueryable<User>()
               .Where<User>( user=> user.Disabled == false)
               .Join<User, Companies, string, 
                    YourCustomResponse>(_companyService.db.AsQueryable(),
                                        user => user.CompanyId,
                                        company => company.Id,                                                                                         
                                        (user, company) => new YourCustomResponse                                                                                       
                                        {
                                             Name = company.Name,
                                             // Other fields in YourCustomResponse
                                        }).ToList();

Collectives™ on Stack Overflow

How to join multiple collections using Mongodb C# driver

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related