C# MongoDB Filter returns the whole object

user_16583725 0
Viewed 80

I'm trying to create a MongoDB filter in C#. For example i have a JSON object like this :

  "Username": "Tinwen",
  "Foods": [
    {
      "Fruit": "Apple",
      "Amount": 1
    },
    {
      "Fruit": "Banana",
      "Amount": 2
    },
    {
      "Fruit": "Mango",
      "Amount": 3
    },
    {
      "Fruit": "Strawberry",
      "Amount": 3
    }
  ]
}

And i want to create a filter that returns only the objects in the array with Amount == 2 || Amount == 3:

{
  "Username": "Tinwen",
  "Foods": [
    {
      "Fruit": "Banana",
      "Amount": 2
    },
    {
      "Fruit": "Mango",
      "Amount": 3
    },
    {
      "Fruit": "Strawberry",
      "Amount": 3
    }
  ]
}

I've already tried filter like this :

var amountFilter= Builders<MyObject>.Filter.ElemMatch(
    m => m.Foods,
    f => f.Amount == 2 || f.Amount == 3);

And this one :

var expected = new List<int>();
expected.Add(2);
expected.Add(3);
var amountFilter = Builders<MyObject>.Filter.And(Builders<MyObject>.Filter.ElemMatch(
    x => x.Foods, Builders<Foods>.Filter.And(
        Builders<Foods>.Filter.In(y => y.Amount, expected))));

But every time it returns me the whole object (with the full array). For now i'm using LinQ like this:

List<MyObject> res = _messageCollection.Find(amountFilter).ToEnumerable().ToList();
foreach (var msg in res)
{
   for (int j = 0; j < msg.Foods.Count; j++)
   {
       if (!expected.Contains(msg.Foods[j].Amount))
       {
          msg.Foods.RemoveAt(j);
       }
   }
}
for (int i = 0; i < res.Count; i++)
{
   if (res[i].Foods.Count == 0)
   {
       res.RemoveAt(i);
   }
}

But I'm pretty sure it can be down using MongoDB filter (and also because it's pretty bad with LinQ). So if anyone have an answer that can help me !

mongodb c#
1 Answers

you can do it with linq like following. but you gotta make the Foods property IEnumerable<Food>

public class User
{
    public string Username { get; set; }
    public IEnumerable<Food> Foods { get; set; }
}

the following query will do a $filter projection on the Foods array/list.

var result = await collection
    .AsQueryable()
    .Where(x => x.Foods.Any(f => f.Amount == 2 || f.Amount == 3))
    .Select(x => new User
    {
        Username = x.Username,
        Foods = x.Foods.Where(f => f.Amount == 2 || f.Amount == 3)
    })
    .ToListAsync();
user_43684850
Related