I am new in using Mongodb C# driver. I have following in my mongodb collection. I need to filter record based on _id something like Select * from item where _id=2 and itemLists.Sizeid=2 in sql.

I tried like 

  var filter = Builders<Item>.Filter.Eq(x => x.temid, ItemId);
  var filter2 = Builders<Item>.Filter.ElemMatch(x => x.itemLists, Builders<ItemList>.Filter.Eq(x => x.Sizeid,sizeId));

var data = await Context.GetCollection("Item").Find(filter&filter2).FirstOrDefaultAsync();

Above code is not giving me expected result. Kindly help.

My MomgoDb Collection Data is as given below:-

{
    "_id" : NumberInt(2),
    "ItemName" : "Shirt",
    "ItemDescripton" : "Men Trouser",
    "itemLists" : [
        {
            "CategoryId" : NumberInt(1),
            "CategpryName" : "Men",
            "ColorId" : NumberInt(1),
            "ColorName" : "Red",
            "InitialQty" : NumberInt(30),
            "AvailableQty" : NumberInt(28),
            "ReserveQty" : NumberInt(2),
            "Price" : 4560.0,
            "OfferPrice" : 670.0,
            "Images" : [
                "/assets/Images/Men/m4.jpg",
                "/assets/Images/Men/m5.jpg"
            ],
            "Sizeid" : NumberInt(2),
            "SizeName" : "XL",
            "DetailId" : NumberInt(0),
            "DeliveryCharges" : NumberInt(20),
            "Brand" : "Allen Solly",
            "CreatedOn" : ISODate("2022-12-07T17:10:19.881 0000"),
            "CreatedBy" : "string",
            "Active" : true
        },
        {
            "CategoryId" : NumberInt(1),
            "CategpryName" : "Men",
            "ColorId" : NumberInt(2),
            "ColorName" : "Blue",
            "InitialQty" : NumberInt(10),
            "AvailableQty" : NumberInt(6),
            "ReserveQty" : NumberInt(4),
            "Price" : 1400.0,
            "OfferPrice" : 1200.0,
            "Images" : [
                "/assets/Images/Men/m3.jpg",
                "/assets/Images/Men/m4.jpg"
            ],
            "Sizeid" : NumberInt(1),
            "SizeName" : "XL",
            "DetailId" : NumberInt(0),
            "DeliveryCharges" : NumberInt(120),
            "Brand" : "Allen Solly",
            "CreatedOn" : ISODate("2022-12-07T17:10:19.881 0000"),
            "CreatedBy" : "string",
            "Active" : true
        }
    ]
}

CodePudding user response：

From your question and comments, you are expecting the queried document to be filtered with the itemLists array for the sizeId criteria.

You need a ProjectionDefinition with ElemMatch in order to achieve it.

ProjectionDefinition<Item> projection = Builders<Item>.Projection
    .Include(x => x.Itemid)
    .Include(x => x.ItemName)
    .Include(x => x.ItemDescripton)
    .ElemMatch(x => x.itemLists, Builders<ItemList>.Filter.Eq(y => y.Sizeid, sizeId));

var data = await _collection.Find(filter & filter2)
    .Project<Item>(projection)
    .FirstOrDefaultAsync();

Demo