CODEWITHSUNDEEP
c#mongodbmongodb-.net-driver
derdo
derdo

Reputation: 1036

How do I write a multikey query in MongoDb and C#?

In our Mongo database we have a collection indexed by "StoreId" and "ItemId". The following query brings back all combinations of product stores (total of 9 documents) included in the "In" list. 

var productQuery = Query.In("ItemId", new BsonArray(new List<int> {1, 2, 3}));
var storeQuery = Query.In("StoreId", new BsonArray(new List<int> {1, 2, 3}));
var queryToBringNineDocuments = Query.And(productQuery, storeQuery);

How do I write a query that brings me back documents with keys in the following item-store tuple list? 

var neededProductStores = new List<Tuple<int, int>>
{
    new Tuple<int, int>(1, 2),
    new Tuple<int, int>(1, 3),
    new Tuple<int, int>(2, 1),
    new Tuple<int, int>(3, 2)
};

var queryToBringFourDocuments = ?;

Upvotes: 4

Views: 4243

Answers (2)

Wade Kaple
Wade Kaple

Reputation: 321

This could also be done with an OR query that accumulates AND queries. It could get pretty ugly if you have a lot of combinations, though.

var neededProductStores = new List<Tuple<int, int>>
                                {
                                    new Tuple<int, int>(1, 2),
                                    new Tuple<int, int>(1, 3),
                                    new Tuple<int, int>(2, 1),
                                    new Tuple<int, int>(3, 2)
                                };

  IMongoQuery[] queries =(from t in neededProductStores
       select Query.And(Query.EQ("ItemId", t.Item1), Query.EQ("StoreId", t.Item2))).ToArray<IMongoQuery>();
  var queryToBringFourDocuments = Query.Or(queries);

Upvotes: 1

Andrew Orsich
Andrew Orsich

Reputation: 53675

It seems to me that there is only one way currently -- create extra field that will contains both ids and query on it

So in your c# class which you store to database you can have:

public string ProductStoreId 
{
  get 
  {
     return string.Format("{0}_{1}",ItemId, StoreId);
  }
  set { } //empty set means that it will be stored to database
}

Then your query will be:

var query = Query.In("ProductStoreId", new BsonArray(new List<string> {"1_2", "1_3",.. }));

Upvotes: 4

Related Questions