How to $lookup with MongoDB C# driver?

Question

How do I perform a $lookup with the MongoDB C# driver? I cannot find it in their driver doc here:

https://docs.mongodb.org/getting-started/csharp/query/

But if I understand this ticket in their JIRA correctly, it should be in the 2.2 version of the driver:

https://jira.mongodb.org/browse/CSHARP-1374

I guess you are referring to a missing "helper" method, which should only be expected as most drivers implementing such things lag behind the features of new server releases. Aggregation pipelines as with normal queries are really just BSON structures. Anything you can construct as BSON Documents can be fed as input to either `.find()` or `.aggregate()`, just as long as it is valid. So just construct the pipeline directly as BSON documents. As long as you have a MongoDB 3.2 server to support `$lookup` of course. Which is the overriding factor here. — Blakes Seven, Feb 25 '16 at 23:04
Note, that there are more complete examples than shown here at [Aggregate $lookup with C#](https://stackoverflow.com/a/50540059/2313887). Shows all the Queryable forms as well as the Fluent Builders of the driver. — Neil Lunn, Oct 03 '19 at 10:53

score 19 · Accepted Answer · answered Feb 25 '16 at 23:05

19

If you use the AsQueryable() extension method on IMongoCollection<T>, you can then use the LINQ interface, as an example.

var query = from p in collection.AsQueryable()
            join o in otherCollection on p.Name equals o.Key into joined
            select new { p.Name, AgeSum: joined.Sum(x => x.Age) };

This was copied from the mongodb csharp driver documentation here http://mongodb.github.io/mongo-csharp-driver/2.2/reference/driver/crud/linq/#lookup

answered Feb 25 '16 at 23:05

Shane

429
3
6

What type is `otherCollection` ? – thepirat000 May 02 '16 at 20:31
3

You need `using MongoDB.Driver.Linq;` namespace to make this work – tigrou Nov 16 '17 at 16:23
@tigrou `System.Linq` was enough for me – Beingnin Mar 08 '19 at 12:19

M. Mennan Kara · Answer 2 · 2018-06-30T10:16:51.343

18

You can also achieve that using the collection.Aggregate().Lookup() method or by adding the lookup to the aggregate stages.

collection.Aggregate()
    .Lookup("foreignCollectionName", "localFieldName", "foreignFieldName", "result");

edited Jun 30 '18 at 10:16

answered Mar 20 '16 at 19:30

M. Mennan Kara

10,072
2
35
39

What about lookup with aggregation pipeline ? – Homam Jul 30 '18 at 08:23
1

@Homam A very detailed answer here: https://stackoverflow.com/questions/50530363/aggregate-lookup-with-c-sharp – M. Mennan Kara Jul 31 '18 at 15:21

score 4 · Answer 3 · edited Aug 09 '18 at 07:43

4

Problem is Lookup requires Projection

Collection.Aggregate().Lookup("foreignCollectionName", "localFieldName", "foreignFieldName","result").Project(Builders<BsonDocument>.Projection.Exclude("_id"))
.ToList()

Then You need it to convert to JSON

String ConvertToJson= res[0].AsBsonDocument.ToJson();
String resultsConvertToJson = ConvertToJson.ToJson();

Then use BSONSerialize and Put it in C# Strongly typed Collection

List<TModel> results= BsonSerializer.Deserialize<List<TMModel>>(resultsConvertToJson);

edited Aug 09 '18 at 07:43

Fredrik Widerberg

3,068
10
30
42

answered Aug 09 '18 at 07:21

Krishnan Prasad

41
1

can you explain if we can get result directly from you 1st part ? without convering to json – Hridoy_089 Jan 20 '21 at 07:44

score 4 · Answer 4 · answered Apr 01 '19 at 17:57

This worked for me:

var collection2 = database.GetCollection<BsonDocument>("dbACESSO");

var match1 = new BsonDocument("$match", new BsonDocument("PartnerId", cliente));
var match2 = new BsonDocument("$match", new BsonDocument("CD_CLIENTE", codCond));

var lookup1 = new BsonDocument { { "$lookup", new BsonDocument { { "from", "GRUPO_UNIDADE" }, { "localField", "CD_GRUPO_UNIDADE" }, { "foreignField", "CD_GRUPO_UNIDADE" }, { "as", "GRUPO" } } } };

var pipeline = new[] { match1, match2, lookup1 };
var result = collection2.Aggregate<BsonDocument>(pipeline).ToList();

score 2 · Answer 5 · answered May 25 '20 at 15:57

other than what the guys already mentioned, there is a type safe overload for the lookup method you can use.

Lookup is an extension method to your local collection, accepts 4 parameters, the first is the foreign collection, the second is an expression to your local field, the third is an expression to your foreign field, the fourth is an expression that map the result of the join to a field in your output type.

_fromTypeCollection.Aggregate<fromType>()
.Lookup<fromType,targetType,outputType>(targetTypeCollection,
fromType => fromType.localFeild, 
targetType => targetType.foreignField, 
outputType => outputType.result);

How to $lookup with MongoDB C# driver?

5 Answers5

Linked