How to apply filter on Array object in MongoDb table using Mongodb.Driver in C#?

Question

I am using MongoDB to store my application data. Now I want to fetch data based on filter on Array object in MongoDB table. as you can see below pic

This is my table tblEmployee. I want to find those records whose GroupList field contains value Group5. When I inserted these records as in mongodb table at that time I stored it as Dictionary<string,dynamic> from C# Code as below.

var clientTest = new MongoClient("mongodb://localhost:XXXX");
var dbTest = clientTest.GetDatabase("Test_DB");
var collectionTest = dbTest.GetCollection<Dictionary<string, dynamic>>("tblEmployee"); 
Dictionary<string, dynamic> obj = new Dictionary<string, dynamic>();
string[] items = { "Group1", "Group2", "Group5", "Group6" };
obj.Add("_id", Guid.NewGuid());
obj.Add("FirstName", "Carlton");
...// Other Props and Values
obj.Add("GroupList", items); // Here value type is String Array.
await collectionTest.InsertOneAsync(obj);

Now, I tried below code read data based on filter as I want.

var clientTest = new MongoClient("mongodb://localhost:XXXX");
                var dbTest = clientTest.GetDatabase("Test_DB");
                var collectionTest = dbTest.GetCollection<Dictionary<string, dynamic>>("tblEmployee");
                var builder = Builders<Dictionary<string, dynamic>>.Filter;
                var filter = builder.AnyIn("GroupList", "Group5"); // Used AnyIn to create IN filter for Array object
                var allDataObj = await collectionTest.FindAsync(filter);
                var allDataList = allDataObj.ToList();

But, in allDataList I got no records, it should return one record (record number 4) which I have highlighted in above image.

Please give me suggestions that How to build Filters for Array object which contains specific value(in my case Array which contain "Group5" string)

Any Help or suggestions would be highly appreciated.

Thanks.

Evk · Accepted Answer · 2018-02-14 08:40:08Z

1

This filter

builder.AnyIn("GroupList", "Group5");

Treats array as array of characters, not strings, because you did not provide generic type and it was inferred as char (because "Group5" string is IEnumerable<char>). For that reason, produced query is not what you would expect. To treat array as array of strings, you need to build filter like this:

var filter = builder.AnyIn("GroupList", new [] {"Group5"});

However you might notice from your screenshot that actual values are stored not in GroupList property, but deeper, inside GroupList > _v, so correct query should be:

var filter = builder.AnyIn("GroupList._v", new [] {"Group5"});

EDIT: as mentioned in comments - it appears that AnyIn in this specific case (for Dictionary<string, dynamic>) only works in older versions of C# driver, while in newer versions it produces strange exception. If you are using last version of C# driver - use suggestions from another answer:

var filter = builder.Eq( "GroupList._v", "Group5"); // query will be { GroupList._v: "Group5" }
var filter = builder.In("GroupList._v", new [] { "Group5" }); // query will be { GroupList._v: { $in: [ "Group5" ] }

edited Feb 14, 2018 at 8:40

answered Feb 14, 2018 at 7:57

Evk

102k8 gold badges156 silver badges204 bronze badges

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

3 Comments

prog1011 Over a year ago

Thanks a lot ... it is working as per above filter. and thanks again for nice explanation.. :)

dnickless Over a year ago

That does not work for me... I'm getting an exception: "The serializer for field 'GroupList._v' must implement IBsonArraySerializer and provide item serialization info."

Evk Over a year ago

@dnickless you are right - it indeed throws this exception in last version of C# driver, but works in older version (I had 2.0.0 installed, not sure when exactly it was changed).

dnickless · Accepted Answer · 2018-02-14 07:58:17Z

1

I would suggest not to use dynamic types if possible since the MongoDB driver simply has no way of inferring the types he is dealing with.

The following two versions give the right result in your specific case:

var filter = builder.Eq( "GroupList._v", "Group5"); // query will be { GroupList._v: "Group5" }
var filter = builder.In("GroupList._v", new [] { "Group5" }); // query will be { GroupList._v: { $in: [ "Group5" ] }

answered Feb 14, 2018 at 7:58

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

Collectives™ on Stack Overflow

How to apply filter on Array object in MongoDb table using Mongodb.Driver in C#?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related