HaBo HaBo - 5 months ago 38
Bash Question

MongoDB aggregation Shell script to MongoC# Driver

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"])
}
}

Answer

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