MongoDB aggregation Shell script to MongoC# Driver

Question

How can I convert this Mongo Shell script to MongoDB C# Driver?

var myItems = []

var myCursor = db.Tickets.aggregate(
   [
      { $match : { TicketProjectID : 49 } },
      { $project: { TicketProjectID:1, TicketID:1, concatValue: { $concat: [ "$Status", " - ", "$Name" ] } } }
    // I will have a list of fields that I need to concatenate at run time. So C# query should support concatenation for "N" number of fields at run-time.
      //{ $group: { _id: null, count: { $sum: 1 } } }

   ],
      { allowDiskUse: true }
)

   //This seems like a ugly performance approach when we are working against 100k results with above match
     while (myCursor.hasNext()) {
         var item = myCursor.next();
         if(item.concatValue.search(/mysearchkey/i) > -1)
             {
                myItems.push(item.TicketID)
             }
    }    
    myItems

or is there a better way to do the string search in concatenated projection instead of foreach in cursor, as some quires might get 50k records.

This is what I have tried so far, (Not using Aggregation)

Note: Trimmed this code to suite for public Q&A sites. So please consider this as Pseudo-code

 var tickets = ticketsCollection.FindSync(filter).ToList();
                string concatinatedValue = string.Empty;
                foreach (var ticket in tickets)
                {
                    foreach (var field in customFieldsForThisProject)
                        concatinatedValue += ticket[field.Replace(" ", "_")];

                  if(concatinatedValue.StripHtml().contains("MysearchWord"))
                 {
                   TikectIdList.Add(ticket["TicketID"])
                 }
                }

@KDecker updated question with what I have tried. I did not try using Aggregation with C# Driver. — HaBo
– HaBo, Commented Jun 9, 2016 at 16:46

HaBo · Accepted Answer · 2016-06-20 11:02:14Z

Thanks to @Nikola.Lukovic, working on his pseudo-code, I came up with this working solution.

Approach one: fully using C# Driver

var ticketsCollection = _mongoConnect.Database.GetCollection<BsonDocument>("Tickets");

            var dbResult = from ticket in ticketsCollection.AsQueryable()
                select new
                {
                    TicketProjectID = ticket["TicketProjectID"],
                    TicketID = ticket["TicketID"],
                    ConcatValue = ticket["Status"] + (string) ticket["Name"]
                };
            var matches = from dbr in dbResult
                where dbr.ConcatValue.Contains(searchKey)
                where dbr.ConcatValue.StartsWith(searchKey)
                select dbr;

This will not work for my scenario as fields I am trying to concatenate are if type string, but $add will only work with numeric and date types.

Approach two: using RunCommand and passing straight Shell command. This will work for all datatypes. And works for my need as well.

        var projectCommand =
            BsonDocument.Parse(
                "{ $project: { _id: -1, TicketProjectID:1, TicketID:1, concatValue: { $concat: [ \"$Status\", \" - \", \"$Name\" ] } } }");
        var matchCommand =
            BsonDocument.Parse("{ $match: {concatValue: { $regex: '" + searchKey + "', $options: 'i'} } }");

        var pipeline = new[] {projectCommand, matchCommand};
        var result = ticketsCollection.Aggregate<BsonDocument>(pipeline).ToList();
        if (result.Count > 0)
            return result.Select(x => (int)x["TicketID"]).ToList();
        return null;

Nikola.Lukovic · Accepted Answer · 2016-06-13 13:52:02Z

2

+50

Edited according to the given comment

If you can use AsQueryable() you can get the values like this:

var dbResult = from ticket in ticketsCollection.AsQueryable()
               where ticket.TicketProjectID == 49
               select new 
               {
                   TicketProjectID = ticket.TicketProjectID,
                   TicketID = ticket.TicketID,
                   ConcatValue = ticket.Status + " - " + ticket.Name
               };

and than later you can do something like this:

var result = from dbr in dbResult
             where dbr.ConcatValue.Contains("something") //or
             where dbr.ConcatValue.StartsWith("something")//or you can use regex
             select dbr;

Note: For some reason both Status and Name properties from type Ticket need to be of a type String for concatenation to work since mongo driver won't recognize the call to ToString() from some other type.

If you want to concatenate properties from some other types you could get them separately from the db and than concat them locally.

note, i'm not that good with mongo shell i could mess something up but you can see in which way you could go

Alternatively you could write your shell command like this and put it in a string:

var command = @"db.Tickets.aggregate(
[
    { $project: { TicketProjectID:1, TicketID:1, concatValue: { $concat: [ "$Status", " - ", "$Name" ] } } },
    { $match : { TicketProjectId : 49, concatValue : { $regex : /mysearchkey/i } } }
],
{ allowDiskUse : true }
);";

then execute it in c# with RunCommandAsync method from MongoDatabase.

var result = await mongoDatabase.RunCommandAsync<BsonDocument>(BsonDocument.Parse(command));

edited Jun 13, 2016 at 13:52

answered Jun 13, 2016 at 10:50

Nikola.Lukovic

1,3551 gold badge16 silver badges35 bronze badges

7 Comments

HaBo Over a year ago

TicketProjectID = 1 indicates include this field in result and ignore rest.

HaBo Over a year ago

I need this dynamic, "ticket.Status + " - " + ticket.Name" I will have a list of fields that I need to concatenate, so how can I construct this concatenation at run time?

Nikola.Lukovic Over a year ago

then i think that your current solution is what you get, unless you have a possibility to inject something like LinqToQueryString between your query and mongodb driver

HaBo Over a year ago

can I execute Shell script as is from C#?

HaBo Over a year ago

Had to make some more changes, but could finally get what I need

|

Collectives™ on Stack Overflow

MongoDB aggregation Shell script to MongoC# Driver

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related