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.

Solution

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.

Comments

[dcg]

Why not using LINQ?

[codecodeNinja]

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

[jazb]

.Contains(xyz) is certainly supported...not sure about Containts()...

[jazb]

docs.microsoft.com/en-us/dotnet/api/…

[Wan B.]

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?

[codecodeNinja]

@WanBachtiar it does not have any error, it shows the syntax error if I use second $lookup

[codecodeNinja]

@JohnB I tried with .Contain() and it works but another problem is using Linq join is inner join. I use .DefaultIfEmpty() but it throw the error: $project or $group does not support {document}.