CODEWITHSUNDEEP
c#mongodbmongodb-.net-driver
Adrian Rosca
Adrian Rosca

Reputation: 7432

How to $lookup with MongoDB C# driver?

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

Upvotes: 13

Views: 35130

Answers (6)

Prophet Lamb
Prophet Lamb

Reputation: 610

Personally I agree @Shane 's answer, using the IMongoQueryable<T> interface is the least effort.

Nonetheless, you can use a typesafe lookup pipeline aswell, but you need two more models inheriting from the inner model.

Let's join pupils and their addresses. But first sample data:

pupils

{ _id: { $oid: 0 }, name: "Homer Simpson", addressId: { $oid: 10 } },
{ _id: { $oid: 1 }, name: "Marge Simpson", addressId: { $oid: 10 } },
{ _id: { $oid: 2 }, name: "Ned Flanders", addressId: { $oid: 11 } },

addresses

{ _id: { $oid: 10 }, address: "742 Evergreen Terrace", city: "Springfield" },
{ _id: { $oid: 11 }, address: "740 Evergreen Terrace", city: "Springfield" },

The pipeline first collects all pupils per address, then flattens the addresses and pupils into a list of n*1-1 relations.

db.adresses.aggregate([{
  $lookup:
  {
    from: "pupils",
      localField: "_id",
        foreignField: "addressId",
        as: "pupil",
    }
  },
  { $unwind: "$pupil" },
])

$lookup stage produces a 1-n relation:

{ _id: { $oid: 10 }, address: "742 Evergreen Terrace", city: "Springfield", pupil:
  [
    { _id: { $oid: 0 }, name: "Homer Simpson", addressId: { $oid: 10 } },
    { _id: { $oid: 1 }, name: "Marge Simpson", addressId: { $oid: 10 } },
  ],
},
{ _id: { $oid: 11 }, address: "740 Evergreen Terrace", city: "Springfield", pupil: 
  [
    { _id: { $oid: 2 }, name: "Ned Flanders", addressId: { $oid: 11 } },
  ],
},

$unwind flattens the pupil array, and "copies" the rest

{ _id: { $oid: 10 }, address: "742 Evergreen Terrace", city: "Springfield", pupil:
  { _id: { $oid: 0 }, name: "Homer Simpson", addressId: { $oid: 10 } },
},
{ _id: { $oid: 10 }, address: "742 Evergreen Terrace", city: "Springfield", pupil: 
  { _id: { $oid: 1 }, name: "Marge Simpson", addressId: { $oid: 10 } },
},
{ _id: { $oid: 11 }, address: "740 Evergreen Terrace", city: "Springfield", pupil:
  { _id: { $oid: 2 }, name: "Ned Flanders", addressId: { $oid: 11 } },
},

All in all we see 4 different models here

  1. Pupil := { name, addressId }
  2. Address := { address, city }
  3. AddressWithPupils := Address ∧ { pupil = {Pupil} }
  4. AddressWithPupil := Addes ∧ { pupil = Pupil }

Notice that the set of pupils is named pupil in 3 as well as the single pupil in 4. this is required due to the unwind stage unwinding the same field name.

record Pupil(ObjectId Id, string Name, ObjectId AddressId);
record Address(ObjectId Id, string Address, string City);
record AddressWithPupils(ObjectId Id, string Name, string City, IEnumerable<Pupil> Pupil) : Address(Id, Name, City);
record AddressWithPupil(ObjectId Id, string Name, string City, Pupil Pupil) : Address(Id, Name, City);

Now let's build our query with the C# driver.

var pipeline = new EmptyPipelineDefinition<Address>()
  .Lookup(_pupils, a => a.Id, p => p.AddressId, (AddressWithPupils as) => as.Pupil)
  .Unwind(as => as.Pupil, new AggregateUnwindOptions<AddressWithPupil>());
var adressWithPupilCursor = _address.Aggregate(pipeline);

We must aid the type inference at a few places to choose the correct models, like explicitly specifying the output type of the lookup stage as AddressWithPupils and the output type of the unwind stage as AddressWithPupil.

Other than that, this works.

Upvotes: 1

Munzer
Munzer

Reputation: 2318

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

Upvotes: 2

Rogerio Azevedo
Rogerio Azevedo

Reputation: 794

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

Upvotes: 4

Krishnan Prasad
Krishnan Prasad

Reputation: 41

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

Upvotes: 4

M. Mennan Kara
M. Mennan Kara

Reputation: 10222

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

Upvotes: 18

Shane
Shane

Reputation: 459

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

Upvotes: 22

Related Questions