Not able to query data

[jithen_dtk]

Hi I am using MongoDB.Driver.Legacy in C#

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);
            }

        }

[James_Kovacs]

Hi, @jithen_dtk,

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.

Sincerely,
James

[jithen_dtk]

@jithen_dtk

offerMasters.Find(new BsonDocument()).ToList();

Hi James,

I have tried all types of variations, please see my below code all take 16 seconds to fetch 1800 records, is there a way to reduce the time

    static async Task Main(string[] args)
    {
        MongoClient dbClient = new MongoClient("mongodb://groxxx:ilkqWPxxxLL@staging.groxxxh.io:20007/?authSource=grxxxxg&readPreference=primary&appname=MongoDB%20Compass&directConnection=true&ssl=false");

        IMongoDatabase db = dbClient.GetDatabase("grooshstag");
        var offerMasters = db.GetCollection<BsonDocument>("OfferMasters");

        ////--- 
        var bsonValues = await(await offerMasters.FindAsync(new BsonDocumentFilterDefinition<BsonDocument>(new BsonDocument()))).ToListAsync();
        Console.WriteLine(bsonValues.Count);
        //// takes 16 seconds

        //
        var rawCollection = db.GetCollection<RawBsonDocument>("OfferMasters");
        var rawValues = await (await rawCollection
            .FindAsync(new BsonDocumentFilterDefinition<RawBsonDocument>(new BsonDocument()))).ToListAsync();

        Console.WriteLine(rawValues.Count + " raw values took ");
        // takes 16 seconds

        ///
        var typedCollection = db.GetCollection<OfferMaster>("OfferMasters");
        var typedValues = await (await typedCollection.FindAsync(new BsonDocument())).ToListAsync();
        Console.WriteLine(typedValues.Count);
        ///takes 16 seconds

        //---
        var resultDoc = offerMasters.Find(new BsonDocument()).ToList();
        foreach (var item in resultDoc)
        {
            Console.WriteLine(item.ToString());
        }
        //// takes 16 seconds
    }

[James_Kovacs]

Hi, @jithen_dtk,

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?

Sincerely,
James

[jithen_dtk]

Hi James,

Thank you for the response.

Below are my answers to your queries

1. What is the average size of your documents in this collection? - 10 - 15 columns mostly string
2. What is the round-trip time to your MongoDB cluster? - 15 seconds
3. 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#
4. 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
                    };

Thanks,
Jithen