How to create join with embedded string array in MongoDB .net?

Question

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

ntohl · Accepted Answer · 2019-10-21 12:03:52Z

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

answered Oct 21, 2019 at 12:03

ntohl

2,1251 gold badge30 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Oğuz KURTCUOĞLU Over a year ago

Thank you so much.. This example solved my problem :) Thanks again...

timunix Over a year ago

@ntohl: I get the following error: "No Overload of the aggregate method takes 0 arguments". How can I fix this?

timunix Over a year ago

I have Visual Studio 2019 Version 16.2.3. Going to update to Version 16.3.6.

ntohl Over a year ago

@timunix With the newest version (2.9.2) there is still a test code, where there is no problem with Aggregate(). github.com/mongodb/mongo-csharp-driver/blob/… Check out from that file, if you missing a using.

timunix Over a year ago

@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<Category>.Filter.Eq("MyId", id); var collection = db.GetCollection<Category>("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?

|

Collectives™ on Stack Overflow

How to create join with embedded string array in MongoDB .net?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related