0

I have two collections Product and Categories. A product can have multiple categories. Product is have a string array for keep category ids (as name Product.Categories).

I want to select products with category details. Note: I'm using MongoDB .Net Driver. Can I do this with Linq query?

Products Collection: `

{
  _id : "product_1",
  title : "Product Title 1",
  categories : ["category_1", "category_2"]
},
{
  _id : "product_2",
  title : "Product Title 2",
  categories : ["category_2"]
}

Categories Collection:

{
  _id: "category_1",
  name : "Category 1 Name",
},
{
  _id: "category_2",
  name : "Category 2 Name",
}

I want to result like below:

{
  _id : "product_1",
  title :"Product Title 1",
  categories : [
     {_id = "category_1", name="Category 1 Name"},
     {_id = "category_2", name="Category 2 Name"},
  ]
},
{
  _id : "product_2",
  title :"Product Title 2",
  categories : [
     {_id = "category_2", name="Category 2 Name"},
  ]
}
Oğuz KURTCUOĞLU
  • 143
  • 2
  • 14

1 Answers1

2

It's basically a join. Which is a Lookup aggregate in C# side. I believe you want the following>

public class Category
{
    public string _id { get; set; }
    public string name { get; set; }
}

public class Product
{
    public string _id { get; set; }
    public string title { get; set; }
    public string[] categories { get; set; }
}

public class AggregatedProduct
{
    [BsonElement("_id")]
    public string Id { get; set; }
    [BsonElement("title")]
    public string Title { get; set; }
    [BsonElement("categories")]
    public Category[] Categories { get; set; }
}


string connectionString = "mongodb://localhost:27017";
var client = new MongoClient(connectionString);

var db = client.GetDatabase("test");
var products = db.GetCollection<Product>("Products");
var categories = db.GetCollection<Category>("Categories");
var resultOfJoin = products.Aggregate().Lookup(foreignCollection: categories, localField: x => x.categories,
    foreignField: x => x._id, @as: (AggregatedProduct pr) => pr.Categories).ToList();
ntohl
  • 2,067
  • 1
  • 28
  • 32
  • Thank you so much.. This example solved my problem :) Thanks again... – Oğuz KURTCUOĞLU Oct 21 '19 at 19:41
  • @ntohl: I get the following error: "No Overload of the aggregate method takes 0 arguments". How can I fix this? – timunix Oct 29 '19 at 07:14
  • @timunix I guess that is some kind of version issue – ntohl Oct 29 '19 at 07:27
  • 1
    I have Visual Studio 2019 Version 16.2.3. Going to update to Version 16.3.6. – timunix Oct 29 '19 at 07:40
  • @ntohl: I updated Visual Studio 2019 to version 16.3.6 and the MongoDb-Driver from version 2.9.1 to 2.9.2. Still no fix, though. – timunix Oct 29 '19 at 08:10
  • @OğuzKURTCUOĞLU: What MongoDB driver version are you using? – timunix Oct 29 '19 at 08:12
  • 1
    @timunix With the newest version (2.9.2) there is still a test code, where there is no problem with `Aggregate()`. https://github.com/mongodb/mongo-csharp-driver/blob/d3c29e0f3338ebed52b0ef3f5d95c2c1d9092ce1/tests/MongoDB.Driver.Tests/AggregateFluentBucketTests.cs#L78-L79 Check out from that file, if you missing a `using`. – ntohl Oct 29 '19 at 08:21
  • 1
    @ntohl It worked for me BUT how do I filter my collection before I carry out products.Aggregate(). This is something I couldn't do because if I filter like this: var filter = Builders.Filter.Eq("MyId", id); var collection = db.GetCollection("Categories"); var result = collection.Find(filter); ........ I won't get an IMongoCollection back but a List. And a List is not compatible with the Aggregate method used here. How can I bypass this? – timunix Nov 11 '19 at 10:17
  • you need a `Match` between `Aggregate` and `Lookup`. This will aggregate only the matching categories. https://stackoverflow.com/a/50905601/1859959 – ntohl Nov 11 '19 at 10:34