MongoDb/C#过滤器并获取所有子文档 [英] MongoDb / C# filter and get all subdocuments

问题描述

我在查询Mongo-DB集合时遇到困难.

I'm having difficulties querying a Mongo-DB collection.

我正在使用的文档

public class Customer
{
    public ObjectId Id { get; set; }

    public int CustomerId { get; set; }

    public List<Address> Addresses { get; set; }
}

public class Address
{
    public string Name { get; set; }
}

一些样本数据

{
    CustomerId: 2,
    Addresses: [
        {
            Name: "Daniel"
        },
        {
            Name: "Eric"
        },
        {
            Name: "Dan"
        }
        ]
}

我现在想通过CustomerId查询文档，并过滤一些Addresses-values以返回所有具有％Dan％之类名称的地址.由于地址集合可能非常庞大，我想在查询时已经过滤掉这些地址

I now want to query the documents by the CustomerId and filter some of the Addresses-values to return all Addresses with a Name like %Dan%. As the address-collection can be huge I want to already filter those addresses at query-time

var mdb = mongoClient.GetDatabase("test");
var collection = mdb.GetCollection<WebApi>("Customer");
var builder = Builders<Customer>.Filter;
var searchTerm = "Dan";

像这样进行查询时，它可以工作，但可以保存所有地址:

When querying like that it works but it holds all addresses:

var filter = builder.And(builder.Eq("InstallationId", 2),
                         builder.Regex("Addresses.Name", new BsonRegularExpression(".*" + searchTerm + ".*", "i")))
var result = collection.Find(filter).FirstOrDefault();

我想要得到的是:

[
    {
        Name: "Daniel"
    },
    {
        Name: "Dan"
    }
]

或

{
    CustomerId: 2,
    Addresses: [
        {
            Name: "Daniel"
        },
        {
            Name: "Dan"
        }
        ]
}

我也尝试了Aggregate/Match/Project的几种方法，但无法使其正常工作.由于未在驱动程序上实现IndexOf，因此"AsQueryable"也不起作用.

I also tried several approaches with Aggregate/Match/Project but can't get it to work. "AsQueryable" also does not work as IndexOf is not implemented on the driver.

collection.AsQueryable().Where(x => x.CustomId == 2).SelectMany(x =>
                x.Addresses.Where(a => a.Name.IndexOf(searchTerm, StringComparison.InvariantCultureIgnoreCase) >= 0).ToList();

使用的版本:

• MongoDB.Driver v 2.6.1(也是Bson)
• .Net Framework 4.5.2

推荐答案

这应该可以帮助您:

var result = collection
    .Aggregate()
    .Match(c => c.CustomerId == 2)
    .Project(c => new
        {
            c.CustomerId,
            Addresses = c.Addresses.Where(a => a.Name.IndexOf(searchTerm) != -1)
        })
    .ToList();

驱动程序会将其翻译为:

The driver will translate this into:

db.Customer.aggregate([{
    $match: { CustomerId: 2 }
}, {
    $project: {
        CustomerId: "$CustomerId",
        Addresses: {
            $filter: {
                input: "$Addresses",
                as: "a",
                cond: {
                    $ne: [ { $indexOfBytes: [ "$$a.Name", "Dan" ] }, -1 ]
                }
            }
        },
        _id: 0
    }
}])

对于不区分大小写的版本，将来可以选择使用$ regex(请参见 https://jira.mongodb.org/browse/SERVER-11947 ).但是，您现在可以使用ToUpper():

For the case-insensitive version, there will be a the option to use $regex at some stage in the future (see https://jira.mongodb.org/browse/SERVER-11947). However, what you can do just now is use ToUpper():

var result = collection
    .Aggregate()
    .Match(c => c.CustomerId == 2)
    .Project(c => new
        {
            c.CustomerId,
            Addresses = c.Addresses.Where(a => a.Name.ToUpper().IndexOf(searchTerm.ToUpper()) != -1)
        })
    .ToList();

这篇关于MongoDb/C#过滤器并获取所有子文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！