Mongodb c#驱动连接查询

本文关键字：连接 查询 Mongodb | 更新日期: 2023-09-27 18:10:46

public class Person
{
    public ObjectId _id { get; set; }
    public int AddressID { get; set; }
    public int Age { get; set; }
    public Person Father { get; set; }
    public string ID { get; set; }
    public double Income { get; set; }
    public string Name { get; set; }
}

public class Address
{
    public ObjectId _id { get; set; }
    public int HouseNo { get; set; }
    public int ID { get; set; }
    public string Street { get; set; }
}

如何按街道获得收入总额?使用mongodb c#驱动程序

按年龄获得收入。

var personcollection = this.cdb.GetCollection<Person>("person");
var aggregate = personcollection.Aggregate()
            .Group(new BsonDocument { { "_id", "$Age" }, { "sum", new BsonDocument("$sum", "$Income") } });
 var results = await aggregate.ToListAsync();

但是我知道如何为单个查询链接两个文档。

谢谢你的帮助

Mongodb c#驱动连接查询

这听起来像是模式设计缺陷。

您应该将您的地址文档嵌入到您的Person文档中:

public class Person
{
    public ObjectId _id { get; set; }
    public Address Address { get; set; }
    public int Age { get; set; }
    public Person Father { get; set; }
    public string ID { get; set; }
    public double Income { get; set; }
    public string Name { get; set; }
}

public class Address
{
    public ObjectId _id { get; set; }
    public int HouseNo { get; set; }
    public int ID { get; set; }
    public string Street { get; set; }
}

您可以轻松地执行请求的查询(修改你现有的查询)

var personcollection = this.cdb.GetCollection<Person>("person");
var aggregate = personcollection.Aggregate()
            .Group(new BsonDocument { { "_id", "$Address.Street" }, { "sum", new BsonDocument("$sum", "$Income") } });
 var results = await aggregate.ToListAsync();

注意使用点表示法到达嵌入文档内部- Address.Street

我知道这是一个旧线程-但我正在编写一个有关'查找'的问题，我发现了这个。如果有人通过搜索方式到达，值得注意的是，MongoDB中的"join"管道现在已经到位。

如果你有合适的MongoDB和驱动程序版本(分别为3.2和2.2 -我认为)，那么你可以在聚合管道中使用'lookup'来连接ID上的两个表。

我是这样查找的:

MongoDB 

db.dbACESSO.aggregate([
{$match: {PartnerId: "2021", CD_CLIENTE: 4003}},
{$lookup: {from: "GRUPO_UNIDADE", localField: "CD_GRUPO_UNIDADE", foreignField: "CD_GRUPO_UNIDADE", as: "GRUPO"}},
{$lookup:{from: "UNIDADE", localField: "CD_UNIDADE", foreignField: "CD_UNIDADE",as: "UNIDADE"}},
{$unwind: "$GRUPO"},
{$unwind: "$UNIDADE"},
{ $project: { _id: 0, CD_CLIENTE : 1, CD_ACESSO : 1, NOME : 1, EMAIL : 1, FG_KIPER_MOBILE : 1, CD_GRUPO_UNIDADE : 1, CD_UNIDADE : 1, 
    GRUPO: "$GRUPO.NM_DESCRICAO", UNIDADE : "$UNIDADE.NM_DESCRICAO", 
    NU_TELEFONE: { $cond: [{ $eq : ["$NU_TELEFONE", { }] }, "", "$NU_TELEFONE"] }, 
    TAG: { $cond: [{ $eq: ["$NU_KIPER_TAG", { }] }, 0, 1] }, 
    CONTROLE: { $cond: [{ $eq: ["$NU_KIPER_RF", { }] }, 0, 1] }, 
    APPATIVO: { $cond: [{ $eq: ["$KEY_HASH", { }] }, "", "$KEY_HASH"] } } 
}   
])

c#司机

var match = new BsonDocument { { "$match", new BsonDocument { { "PartnerId", cliente }, { "CD_CLIENTE", codCond } } } };
            var lookup1 = new BsonDocument { { "$lookup", new BsonDocument { { "from", "GRUPO_UNIDADE" }, { "localField", "CD_GRUPO_UNIDADE" }, { "foreignField", "CD_GRUPO_UNIDADE" }, { "as", "GRUPO" } } } };
            var lookup2 = new BsonDocument { { "$lookup", new BsonDocument { { "from", "UNIDADE" }, { "localField", "CD_UNIDADE" }, { "foreignField", "CD_UNIDADE" }, { "as", "UNIDADE" } } } };
            var unwind1 = new BsonDocument("$unwind", "$GRUPO");
            var unwind2 = new BsonDocument("$unwind", "$UNIDADE");
            var project = new BsonDocument
            {
                {
                    "$project", new BsonDocument
                    {
                        { "_id", 0},
                        { "CD_CLIENTE", 1},
                        { "CD_ACESSO", 1 },
                        { "NOME", 1},
                        { "EMAIL", 1 },
                        { "FG_KIPER_MOBILE", 1 },
                        { "CD_GRUPO_UNIDADE", 1 },
                        { "CD_UNIDADE", 1 },
                        { "GRUPO", "$GRUPO.NM_DESCRICAO" },
                        { "UNIDADE", "$UNIDADE.NM_DESCRICAO" },                        
                        { "NU_TELEFONE", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$NU_TELEFONE", new BsonDocument { } } }}, "","$NU_TELEFONE" } }}},
                        { "TAG", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$NU_KIPER_TAG", new BsonDocument { } } }}, 0, 1 } }}},
                        { "CONTROLE", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$NU_KIPER_RF", new BsonDocument { } } }}, 0, 1 } }}},
                        { "APP", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$FG_KIPER_MOBILE", false } }}, 0, 1 } }}},
                        { "APPATIVO", new BsonDocument{{ "$cond", new BsonArray{new BsonDocument{{"$eq", new BsonArray{ "$KEY_HASH", new BsonDocument { } } }}, "", "$KEY_HASH" } }}}
                    }
                }
            };
            var pipeline = new[] { match, lookup1, lookup2, unwind1, unwind2, project };
            var result = collection.Aggregate<BsonDocument>(pipeline).ToList();
            var lista = JsonConvert.DeserializeObject<List<UsuariosAcessos>>(result.ToJson()).ToList();
相关文章：