I am trying to link 4 different tables and query, I get "“The GroupJoin query operator is not supported.” error when I try looping the query.
Below is my code, please can some one help, what I need is straight farward query individual tables from MongoDB and then apply query to it
MongoClient client1 = new MongoClient(“mongodb://grXXXhstag:ilXXXXXXXsh.io:27017/?authSource=grXXg&readPreference=primary&appname=MongoDB%20Compass&directConnection=true&ssl=false”);
var server1 = client1.GetServer();
var db1 = server1.GetDatabase(“grooshstag”);
var offerCollection = db1.GetCollection(“OfferMasters”);
var locationCollection = db1.GetCollection(“LocationMaster”);
var userCardCollection = db1.GetCollection(“UserCardMaster”);
var bankCardCollection = db1.GetCollection(“BankCardMaster”);
var offerList = offerCollection.AsQueryable().Where(m => m.ActiveStatus == true);
var locationMasterList = locationCollection.AsQueryable().Where(k => k.ActiveStatus == true);
var userCardMasterList = userCardCollection.AsQueryable().Where(n => n.ActiveStatus == true && n.UserId == "60dade06ca166e25a8efdf58");
var bankCardMasterList = bankCardCollection.AsQueryable().Where(k => k.ActiveStatus == true);
Console.WriteLine("\n\nofferList => " + offerList.Count());
List<UserCardOffer> userCardOffersList = new List<UserCardOffer>();
if (offerList != null)
{
var query = from o in offerList
join l in locationMasterList.AsQueryable() on o.LocationId equals l.LocationId into locationData
join u in userCardMasterList.AsQueryable() on o.BankId equals u.BankId into cardData
join b in bankCardMasterList.AsQueryable() on o.BankId equals b.BankId into bankData
select new UserCardOffer()
{
offerMaster = o,
UserId = cardData.FirstOrDefault().UserId,
First6Digits = cardData.FirstOrDefault().First6Digits,
Last4Digits = cardData.FirstOrDefault().Last4Digits,
LocationName = locationData.FirstOrDefault().LocationName,
LocationAddress = locationData.FirstOrDefault().LocationAddress,
LocationType = locationData.FirstOrDefault().LocationType,
CardImagePath = bankData.FirstOrDefault().CardImagePath
};
foreach (var e in query)
{
Console.WriteLine("user id" + e.UserId);
}
}
Welcome to the MongoDB Community Forums. I understand that you’re attempting to join 4 collections using LINQ in the legacy C# driver. Unfortunately the LINQ implementation in the legacy C# driver does not support this capability. I updated your example to the latest LINQ3 implementation, which has partial support for join (aka $lookup), but encountered an error there as well. This is likely related to CSHARP-4054.
I would encourage you to review our documentation on MongoDB data modelling as you appear to be modelling your domain using RDBMS concepts such as tables and foreign keys. It is often more efficient and logical to model your domain as documents, which dramatically reduces the need for joins between related entities as the related entities are nested and stored as subdocuments.
If you are unable to refactor your data model, then you can re-write your logic as the individual queries with the results of the previous ones providing input for subsequent ones. This would involve additional roundtrips to the database, which isn’t ideal, but is a straightforward technique. Another option would be to use coll.Aggregate(pipeline) to write your own aggregation pipeline in MQL. You can find some examples on how to build custom MQL aggregation pipelines in our Atlas Text Search documentation. In this case you would be using $lookup rather than $search, but the custom pipeline building technique is the same.
The time taken to fetch documents depends on a variety of factors including network conditions, query time on the server, data size, and deserialization on the client, just to name a few. Your first query will also be influenced by establishment of connections in the connection pools including TCP socket establishment, SSL/TLS handshake (not applicable in this case), and authentication.
When I tried to reproduce the issue that you’re observing with your above code, I noticed that the time taken is heavily dependent on the size of the BSON documents and the round-trip time to the server. With small documents on a locally running mongod, the query time was sub-second. With large documents and a remote server, the overall time was dominated by the round-trip time to the server.
I would suggest considering and trying the following:
What is the average size of your documents in this collection?
What is the round-trip time to your MongoDB cluster?
If you attempt the same test with small documents containing only an _id, how does this affect the query time?
If you attempt the same test with a mongod instance running on the same machine, how does this affect the query time?
What is the average size of your documents in this collection? - 10 - 15 columns mostly string
What is the round-trip time to your MongoDB cluster? - 15 seconds
If you attempt the same test with small documents containing only an _id , how does this affect the query time? - I do not know how to retrieve selective columns I could not fine working sample code for C#
If you attempt the same test with a mongod instance running on the same machine, how does this affect the query time? - It takes very less time if the MongoDB is local
I tried the below code
var offers = db.GetCollection<BsonDocument>("OfferMasters");
var filter = Builders<BsonDocument>.Filter.Eq("ActiveStatus", true);
var offerDoc = offers.Find(filter);
this takes few milliseconds, but I am unable to join two documents and query, please can you point me to some sample code in C#
I want to do something like this
var query = from o in offerDoc
join l in locationDoc on o.LocationId equals l.LocationId into locationData
join u in userCardDoc on o.BankId equals u.BankId into cardData
join b in bankCardDoc on o.BankId equals b.BankId into bankData
select new UserCardOffer()
{
offerMaster = o,
UserId = cardData.FirstOrDefault().UserId,
First6Digits = cardData.FirstOrDefault().First6Digits,
Last4Digits = cardData.FirstOrDefault().Last4Digits,
LocationName = locationData.FirstOrDefault().LocationName
};